cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tlrodrigues
Regular Visitor

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

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: 

 

locationdatepeople_fully_vaccinated
Brazil15-05-2022                           165,173,312
Brazil16-05-2022                           165,182,832
Brazil17-05-2022 
Brazil18-05-2022                           165,396,479
Brazil19-05-2022                           165,448,319
Spain09-05-2022 
Spain10-05-2022 
Spain11-05-2022                              40,460,123
Spain18-05-2022 
Spain19-05-2022 
United States15-05-2022                           220,743,006
United States16-05-2022                           220,781,260
United States17-05-2022                           220,807,125
United States18-05-2022                           220,811,434
United States19-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]))
))
2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

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.

 

Untitled.png

 

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
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

tamerj1
Super User
Super User

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

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

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

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

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

1.png

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

You can accept all the workable solutions

Awesome! Again, thank you very much!!

Jihwan_Kim
Super User
Super User

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.

 

Untitled.png

 

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
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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? 

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors