Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
)
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.
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
)
)
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
)
)
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
)
)
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!!
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
)
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |