Sum the values of a column considering the last non blank date of another column

[tlrodrigues]

Hello,

I'm super new to Power BI as well as to this community. I'll be very grateful if I can get help with the below: 

I have a table that has columns with countries, dates, and quantity of people vaccinated, but this quantity is cumulative (every day it sums up the new people vaccinated).

I want to find out the total number of people vaccinated, so I have to consider in my sum the last date, but for some countries, the last date is blank, so these countries aren't being considered in the measure that I came up with.

Table mentioned above: 

location	date	people_fully_vaccinated
Brazil	15-05-2022	165,173,312
Brazil	16-05-2022	165,182,832
Brazil	17-05-2022
Brazil	18-05-2022	165,396,479
Brazil	19-05-2022	165,448,319
Spain	09-05-2022
Spain	10-05-2022
Spain	11-05-2022	40,460,123
Spain	18-05-2022
Spain	19-05-2022
United States	15-05-2022	220,743,006
United States	16-05-2022	220,781,260
United States	17-05-2022	220,807,125
United States	18-05-2022	220,811,434
United States	19-05-2022

The result that I want:

Brazil = 165,448,319

Spain = 40,460,123

United States = 220,811,434

Total (result expected with the measure) = 426,719,876

Measure that I created, but that is not working as I expect:

CALCULATE (

SUM (DeathsAndVaccinations[people_fully_vaccinated]),

GENERATE (

VALUES (DeathsAndVaccinations[location]), LASTNONBLANK(DeathsAndVaccinations[date], LASTDATE(DeathsAndVaccinations[date]))

))

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your desired outcome of the visualization looks like, but please check the below picture and the attached pbix file.

Lastnonblank people measure: =
VAR _lastdate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUMX (
            ADDCOLUMNS (
                CROSSJOIN ( VALUES ( Location[location] ), VALUES ( 'Calendar'[Date] ) ),
                "@lastnonbalnkpeoplecount",
                    VAR _currentdate = 'Calendar'[Date]
                    VAR _lastnonblankdate =
                        CALCULATE (
                            LASTNONBLANK (
                                'Calendar'[Date],
                                CALCULATE ( SUM ( Data[people_fully_vaccinated] ) )
                            ),
                            'Calendar'[Date] <= _currentdate
                        )
                    VAR _result =
                        CALCULATE (
                            SUM ( Data[people_fully_vaccinated] ),
                            'Calendar'[Date] = _lastnonblankdate
                        )
                    RETURN
                        _result
            ),
            [@lastnonbalnkpeoplecount]
        ),
        'Calendar'[Date] = _lastdate
    )

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your desired outcome of the visualization looks like, but please check the below picture and the attached pbix file.

Lastnonblank people measure: =
VAR _lastdate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUMX (
            ADDCOLUMNS (
                CROSSJOIN ( VALUES ( Location[location] ), VALUES ( 'Calendar'[Date] ) ),
                "@lastnonbalnkpeoplecount",
                    VAR _currentdate = 'Calendar'[Date]
                    VAR _lastnonblankdate =
                        CALCULATE (
                            LASTNONBLANK (
                                'Calendar'[Date],
                                CALCULATE ( SUM ( Data[people_fully_vaccinated] ) )
                            ),
                            'Calendar'[Date] <= _currentdate
                        )
                    VAR _result =
                        CALCULATE (
                            SUM ( Data[people_fully_vaccinated] ),
                            'Calendar'[Date] = _lastnonblankdate
                        )
                    RETURN
                        _result
            ),
            [@lastnonbalnkpeoplecount]
        ),
        'Calendar'[Date] = _lastdate
    )

[tlrodrigues]

Hi Jihwan_Kim,

It worked, thank you very much! You rock!! I will set it as the Solution!!!

One last question if you don't mind, do you think we can get the same results without creating a new table with the DISTINCT location? Like, include it in the formula itself? 

[Jihwan_Kim]

Hi,

Thank you for your feedback.

I am not sure if I correctly understood your last question, but if you want to show a card visualization, please check the below attached pbix file. The same measure can be put into a card visualization and it shows the expected result.

Thank you.

[tamerj1 · Accepted solution]

Hi @tlrodrigues 

please try

Vaccinated =
SUMX (
    VALUES ( DeathsAndVaccinations[location] ),
    VAR MaxDate =
        CALCULATE ( MAX ( DeathsAndVaccinations[date] ) )
    RETURN
        CALCULATE (
            SUM ( DeathsAndVaccinations[people_fully_vaccinated] ),
            DeathsAndVaccinations[date] = MaxDate
        )
)

[tlrodrigues]

I appreciate the help, @tamerj1. However, I tested and got the same issue.

[tamerj1]

Hi @tlrodrigues 
Actually it is working without adding the date column. The date column can be added as a simple measure (MAX). However, I realized that there are blanks in some rows  that we need to deal with and I added ALLEXCEPT just to be at the safe side. 
The last date measure:

Last Date = 
MAXX ( 
    FILTER ( 
        CALCULATETABLE ( 
            DeathsAndVaccinations, 
            ALLEXCEPT ( DeathsAndVaccinations, DeathsAndVaccinations[location] ) 
        ), 
        DeathsAndVaccinations[people_fully_vaccinated] <> BLANK ( ) 
    ), 
    DeathsAndVaccinations[date] 
)

Number of vaccinated measure:

Vaccinated = 
SUMX (
    VALUES ( DeathsAndVaccinations[location] ),
    VAR MaxDate = [Last Date]
    RETURN
        CALCULATE (
            SUM ( DeathsAndVaccinations[people_fully_vaccinated] ),
            DeathsAndVaccinations[date] = MaxDate
        )
)

[tlrodrigues]

Hi, tamerj1.

It worked, in a short way, impressive. Thank you very much, I was having so much trouble with this.

Now, one question about the community as it was my first post. What solution should I accept? The first one that got to the results? Or the one that worked better (yours)? 

[tamerj1]

You can accept all the workable solutions

[tlrodrigues]

Awesome! Again, thank you very much!!