Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a set of data that I need to have a running total for but I need it to reset based on the value of another column.
The total will increment when one of the columns value is null, once it is not null the total will reset to zero then continue.
Additionally, I need to somehow reset it to zero or group it based on another column (like a category).
I have attached what I was trying to do but it is far out of my abilities...
This measure expression gets your expected results from the example data.
Count Since Last Yes =
VAR thisdatetime =
MIN ( 'Action'[Date] )
VAR lastYes =
CALCULATE (
MAX ( 'Action'[Date] ),
ALL ( 'Action'[Date] ),
'Action'[Date] < thisdatetime,
'Action'[Action] = "Yes"
)
VAR ifblank =
IF (
ISBLANK ( lastYes ),
CALCULATE ( MIN ( 'Action'[Date] ), ALLEXCEPT ( 'Action', 'Action'[Category] ) ) - 1,
lastYes
)
RETURN
IF (
MIN ( 'Action'[Action] ) <> "Yes",
CALCULATE (
COUNTROWS ( 'Action' ),
ALL ( 'Action'[Date] ),
'Action'[Date] <= thisdatetime,
'Action'[Date] > ifblank
) - 1
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
did you get the answer to this?
I ended up making a Powershell script that did the work for me. Nothing else really worked.
Not positive, but this sounds like you are in need of something along the lines of Cthulhu: https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/td-p/509739
@Greg_Decklerthat looks great but these rows are consecutive..
Would you suggest I change the data in a way that would allow this to work? If so, any tips?
Thank you for getting back to me.
Yeah, I think if you add an Index column to this in Power Query that you are pretty much there. To be clear, Cthulhu doesn't work without consequetive data. There pretty much has to be an Index. The only wrinkle you have is that you have to account for the extra "Category" column but that shouldn't be too much of a stretch.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |