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.
I am trying to create a Running Total using COUNTA on two columns to give a running count of statuses at the end of each day. If a Value is in the NEW_VALUE I want to Add 1 to the running total for that value. If it is in the OLD_VALUE column I want to Subtract 1.
CHANGE_DATE | OLD_VALUE | NEW_VALUE |
01/09/2018 00:00 | New | |
03/09/2018 00:00 | New | |
03/09/2018 00:00 | New | Open |
04/09/2018 00:00 | New | |
04/09/2018 00:00 | New | |
04/09/2018 00:00 | New | |
04/09/2018 00:00 | New | Open |
04/09/2018 00:00 | New | Open |
05/09/2018 00:00 | New | |
05/09/2018 00:00 | New | Open |
06/09/2018 00:00 | New | Open |
From the table above (Taking "New" and "Open" as an example) I would expect to see:
(The full Table has many more Statuses)
New | Open | |
01/09/2018 | 1 | |
03/09/2018 | 1 | 1 |
04/09/2018 | 2 | 3 |
05/09/2018 | 1 | 5 |
Using the Measure:
And I know exactly why you get 0 🙂 The empty cells in your table are not really empty. They do hold strings; they might be of zero length but they're still strings. Please replace them with proper BLANK()'s.
Best
Darek
Mate, here's the running total and it does what you wanted. If you want to know what the constituent parts return (the parts with a double underscore __), then just replace the output with the names of the variables.
Running Total = var __visibleDate = SELECTEDVALUE( Dates[Date] ) var __dateExistsInData = NOT ISEMPTY( FILTER( All( Data[CHANGE_DATE] ), Data[CHANGE_DATE] = __visibleDate ) ) var __countOld = CALCULATE( COUNTA( Data[OLD_VALUE] ), Data[OLD_VALUE] <> BLANK(), Dates[Date] <= __visibleDate, ALL( Data ) ) var __countNew = CALCULATE( COUNTA( Data[NEW_VALUE] ), Data[NEW_VALUE] <> BLANK(), Dates[Date] <= __visibleDate, ALL( Data ) ) var __total = __countNew - __countOld return if( __dateExistsInData, __countNew - __countOld )
Please note that I've created a proper Dates table and marked it as such, then joined it to the date column in the Data table. When you put your data on a visual, do not use the CHANGE_DATE (this field should be hidden). Use the Date field from the Dates table, like so:
Best
Darek
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |