Forum Discussion

nauriso1's avatar
nauriso1
Frequent Visitor
10 years ago
Solved

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: with such d...
  • nauriso1's avatar
    nauriso1
    9 years ago

    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] )
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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]))

 

 

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]))))

 

 

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

 

 

Regards,

Xiaoxin Sheng

  • nauriso1's avatar
    nauriso1
    Frequent Visitor
    9 years ago

    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] )
    )
)