Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Is there a secret to using a date table when using running totals?
Note: I'm using Direct Query, a requirement for this report.
I have a table with multiple date fields, which I've unpivoted into a [Date Field] column. And when I use that [Date Field] column for a running total it works fine.
The source table had ID, Date Field, and PFA (whether it is a plan actual or forecasted date).
But I need to group the counts by month (and the hierarchy doesn't seem to work properly with Direct Query). So I've setup a relationship to a Date Table, but whenever I try to use the "date" from the date table (the two on the right below, with the second grouping by month), it breaks the running total.
I'm using two calculations, the first in an "incremental" count just showing the count of dates (filtered by "Actual" in this case) per day (or month when grouped). The second is the cumulative which uses the incremental count.
Inc Actual = CALCULATE(Count('Table'[Date Field]),Filter('Field Lookup Table',[PFA]="Actual"))
**bleep** Actual =
CALCULATE(
[Inc Actual],
FILTER(
ALLSELECTED('Table'[Date Field]),
ISONORAFTER('Table'[Date Field], MAX('Table'[Date Field]), DESC)
)
)
I've tried playing with the formulas but haven't found the secret to getting this to work properly.
Thanks.
Solved! Go to Solution.
I've been banging my head against a wall for nearly a week trying to get this to work. Five minutes after I posted this I think I stumbled upon the solution.
I did three things:
1) Instead of using my incremental measure "Inc - Actual" in the running total formula I calculated it in the running total formula itself.
2) Since I wasn't using my incremental measure, which filtered for "Actual", I added that filter to step 3...
3) Instead of using Count, i used CountA, and added an additional filter to that to filter only the "Actual" items.
Test Runningtotal =
CALCULATE(
COUNTA('Table'[Date Field]),
FILTER(
ALLSELECTED('Date'[YearMonthnumber]),
ISONORAFTER('Date'[YearMonthnumber], MAX('Date'[YearMonthnumber]), DESC)
),FILTER('Lookuptable',[Plan Forecast Actual]="Actual")
)
Thanks.
@Anonymous - Can you post sample data as text and expected output?
They bleeped your measure name!! Shame shame!! 🙂
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
I've been banging my head against a wall for nearly a week trying to get this to work. Five minutes after I posted this I think I stumbled upon the solution.
I did three things:
1) Instead of using my incremental measure "Inc - Actual" in the running total formula I calculated it in the running total formula itself.
2) Since I wasn't using my incremental measure, which filtered for "Actual", I added that filter to step 3...
3) Instead of using Count, i used CountA, and added an additional filter to that to filter only the "Actual" items.
Test Runningtotal =
CALCULATE(
COUNTA('Table'[Date Field]),
FILTER(
ALLSELECTED('Date'[YearMonthnumber]),
ISONORAFTER('Date'[YearMonthnumber], MAX('Date'[YearMonthnumber]), DESC)
),FILTER('Lookuptable',[Plan Forecast Actual]="Actual")
)
Thanks.
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |