Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nauriso1
Frequent Visitor

Running Total Cohort

Hello

 

How to calculate running total for cohort? Using dates for running total calculation is stright forward, but I can't find solution for cohort. I have such data model:

Data Model.PNG

with such data

 Tables.PNG

 

 I want to create running total, so it never exceed count of days from Date Added to list till last date in dimDate table  (values in  column "Days Between DateAddedToList and MaxDateId", which prefferably should be defined as measure in running total calculation) regardless of the last date of dates in Date Transactions. So for an example - item C was added to list in 05. Aug. Last date in dimDate is 8.Aug, so running total should be only for 4 days.

Desired result is below: 
running_total_cohort.PNG

The problem - I can't specify this days count limit in calculation for running total..

 

 There is also link with data and model in Excel
data_for_running_total_cohort

 

Thanks BR

1 ACCEPTED SOLUTION

Thanks for your efort, but that isn't what I wanted, but finally I found by my self a sulotion based on this post in Stack Overflow
http://stackoverflow.com/questions/31661131/dax-cumulative-total-with-date-filters


So in my case I had to remove relationship from dimDays table to fctTrans and write measure:

Runing Total by Days final :=
CALCULATE (
    SUM ( fctTrans[Amount] );
    FILTER (
        VALUES ( fctTrans[Days Between Date Transaction and DateAddedToList] );
        fctTrans[Days Between Date Transaction and DateAddedToList]
            <= MAX ( dimDays[DaysId] )
    );
    FILTER (
        VALUES ( fctTrans[Days Between DateAddedToListAnd MaxDateId] );
        fctTrans[Days Between DateAddedToListAnd MaxDateId] >= MAX ( dimDays[DaysId] )
    )
)

 

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi nauriso1,

 

Based on my understanding, you want to get a running total on visual, right?

You can refer to below steps:

1. Create a summary table.

 

Dax:

Table = ADDCOLUMNS(CROSSJOIN(dimItem,dimDays),"Amount",LOOKUPVALUE(fctTrans[Amount],dimItem[DimItem],[DimItem],fctTrans[Days Between Date Transaction and DateAddedToList],[DaysId]))

Capture.PNG 

 

2. Calculate the running total.(Calculate column)

Dax:

Total Amount = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),AND( 'Table'[DimItem]=EARLIER('Table'[DimItem]),'Table'[DaysId]<=EARLIER('Table'[DaysId]))))

 

Capture2.PNG

 

3. Create a matrix visual and drag the above table’s columns to it.

 Capture3.PNG

Capture4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for your efort, but that isn't what I wanted, but finally I found by my self a sulotion based on this post in Stack Overflow
http://stackoverflow.com/questions/31661131/dax-cumulative-total-with-date-filters


So in my case I had to remove relationship from dimDays table to fctTrans and write measure:

Runing Total by Days final :=
CALCULATE (
    SUM ( fctTrans[Amount] );
    FILTER (
        VALUES ( fctTrans[Days Between Date Transaction and DateAddedToList] );
        fctTrans[Days Between Date Transaction and DateAddedToList]
            <= MAX ( dimDays[DaysId] )
    );
    FILTER (
        VALUES ( fctTrans[Days Between DateAddedToListAnd MaxDateId] );
        fctTrans[Days Between DateAddedToListAnd MaxDateId] >= MAX ( dimDays[DaysId] )
    )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.