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 have a table which brings back values for the last date of the month and the most recent date available in the data set. For example, if the most recent date in the data set is 8/10/2020, it will bring back:
06/30/2020 07/31/2020 8/10/2020
Dimension 1 345 450 465
Dimension 2 1,350 756 820
im doing it with the following formula:
06/30/2020 07/31/2020
Dimension 1 345 450
Dimension 2 1,350 756
where the desired product would be this:
06/30/2020 07/31/2020 8/8/2020
Dimension 1 345 450 365
Dimension 2 1,350 756 210
help?
Solved! Go to Solution.
@sergiod04 Try like
Measure = CALCULATE (lastnonblankvalue(DATE_TABLE[Date],SUM(VALUE))), based on grouping it will choose date. Take month year on axis/row/column
@sergiod04 , try like
Measure = CALCULATE (SUM(VALUE), FILTER(DATE_TABLE,DATE_TABLE[Date] = max(DATE_TABLE[Date])))
or
Measure = CALCULATE (SUM(VALUE), FILTER(DATE_TABLE,DATE_TABLE[Date] = lastdate(DATE_TABLE[Date])))
Hi @amitchandak ,
Thanks for responding. With both the MAX and LASTDATE, it is returning all dates, not just the last day of the month + the most recent one. 😕
end of month was doing the trick of returning only the last day of the month + the last date when i didnt have any date filters applied. the problem is when i apply a date filter that does not land on an end of month.
i appreciate your help.
@sergiod04 Try like
Measure = CALCULATE (lastnonblankvalue(DATE_TABLE[Date],SUM(VALUE))), based on grouping it will choose date. Take month year on axis/row/column
This will work! thank you!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |