topic Re: Sum the values of a column considering the last non blank date of another column in DAX Commands and Tips

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

tlrodrigues — Fri, 27 May 2022 01:56:35 GMT

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]))

))

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

Jihwan_Kim — Fri, 27 May 2022 03:10:43 GMT

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 )

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

tamerj1 — Fri, 27 May 2022 03:12:47 GMT

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 ) )

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

tlrodrigues — Fri, 27 May 2022 10:25:46 GMT

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

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

tamerj1 — Fri, 27 May 2022 11:46:52 GMT

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 ) )

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

tlrodrigues — Fri, 27 May 2022 11:55:16 GMT

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?

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

tlrodrigues — Fri, 27 May 2022 12:08:53 GMT

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)?

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

tamerj1 — Fri, 27 May 2022 12:10:12 GMT

You can accept all the workable solutions

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

tlrodrigues — Fri, 27 May 2022 12:12:43 GMT

Awesome! Again, thank you very much!!

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

Jihwan_Kim — Sat, 28 May 2022 06:48:20 GMT

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.