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.
Hi,
I need to get chart with runnig total hours reported in project by weekt time , I am using this DAX formula :
CumulativeHours =
CALCULATE (
SUM ( Events[HoursRound] ),
FILTER ( ALL ( 'Time'[Week] ), 'Time'[Week] <= MAX ( 'Time'[Week] ) )
)
But the problem is that in time table I have weeks where there are no hours reported yet for this reason I get chart like first one
I would like to stop runnig total in courrent week.
Thanks for help.
Solved! Go to Solution.
Here is my theory:
CumulativeHours =
VAR LastEvent = LASTNONBLANK ( Time[Week], CALCULATE ( SUM ( Events[HoursRound] ) ) )
RETURN
CALCULATE (
SUM ( Events[HoursRound] ),
FILTER ( ALL ( 'Time'[Week] ), 'Time'[Week] <= MAX ( 'Time'[Week] ) && 'Time'[Week] <= LastEvent)
)
Maybe try wrapping your running total in an IF() statement that checks if the date value is the current week or earlier and if yes, returns your cumulative value, and if not, returns BLANK().
An easy way to do this is to add a calc column that checks if the date is => current week and return a Yes or No flag, then use this field in your measure for the filter argument.
Also, you could try setting a visual level filter on the chart you have to only show dates up to the current date/week.
Here is my theory:
CumulativeHours =
VAR LastEvent = LASTNONBLANK ( Time[Week], CALCULATE ( SUM ( Events[HoursRound] ) ) )
RETURN
CALCULATE (
SUM ( Events[HoursRound] ),
FILTER ( ALL ( 'Time'[Week] ), 'Time'[Week] <= MAX ( 'Time'[Week] ) && 'Time'[Week] <= LastEvent)
)
That works, thanks a lot for help !
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |