Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running (Cumulative) total not working when using date table.

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 ...
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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.

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.