Forum Discussion

nhol's avatar
nhol
Advocate II
8 years ago
Solved

Running Total

Hi,   I have 2 years of monthly data on which I'm applying a Running Total measure as written below:   Running Total MEASURE = CALCULATE ( SUM ( CompanyPortalDataBase[PageVisit] ), FILTER ( ...
Data Modeling
Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi nhol,

     

    Please modify the measure like below: 

     

    Running Total MEASURE =
    CALCULATE (
    SUM ( CompanyPortalDataBase[PageVisit] ),
    FILTER (
    ALL ( CompanyPortalDataBase),
    'CompanyPortalDataBase'[Date] <= MAX ( 'CompanyPortalDataBase'[Date] ) && YEAR(CompanyPortalDataBase[Date]) =YEAR(MAX(CompanyPortalDataBase[Date]))
    )
    )

     

     

    Best Regards,
    Qiuyun Yu 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this calculated column formula

     

    =CALCULATE(SUM(CompanyPortalDataBase[PageVisit]),FILTER(CompanyPortalDataBase,CompanyPortalDataBase[Category]=EARLIER(CompanyPortalDataBase[Category])&&CompanyPortalDataBase[Year]=EARLIER(CompanyPortalDataBase[Year])&&CompanyPortalDataBase[Date]<=EARLIER(CompanyPortalDataBase[Date])))/CALCULATE(COUNTROWS(CompanyPortalDataBase),FILTER(CompanyPortalDataBase,CompanyPortalDataBase[Category]=EARLIER(CompanyPortalDataBase[Category])&&CompanyPortalDataBase[Year]=EARLIER(CompanyPortalDataBase[Year])&&CompanyPortalDataBase[Date]<=EARLIER(CompanyPortalDataBase[Date])))

nhol's avatar
nhol
Advocate II
8 years ago

Hi,

 

For some reason it didn't work, I think it's because my [Date] field that I was showing in the Excel resides in a different table. Without the [Date] like that:

=CALCULATE(SUM(Data[PageVisit]),FILTER(Data,Data[WebCat]=EARLIER(Data[WebCat])&&Data[Year]=EARLIER(Data[Year])))

I got the same aggregated number for the entire year on a monthly basis. 

Is there a way to do it with a measure and not a column?

 

Thanks!

Nir H.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

I cannot comment unless i see your file.  Tell me exactly where my result deviates from your expected result.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this calculated column formula

     

    =CALCULATE(SUM(CompanyPortalDataBase[PageVisit]),FILTER(CompanyPortalDataBase,CompanyPortalDataBase[Category]=EARLIER(CompanyPortalDataBase[Category])&&CompanyPortalDataBase[Year]=EARLIER(CompanyPortalDataBase[Year])&&CompanyPortalDataBase[Date]<=EARLIER(CompanyPortalDataBase[Date])))/CALCULATE(COUNTROWS(CompanyPortalDataBase),FILTER(CompanyPortalDataBase,CompanyPortalDataBase[Category]=EARLIER(CompanyPortalDataBase[Category])&&CompanyPortalDataBase[Year]=EARLIER(CompanyPortalDataBase[Year])&&CompanyPortalDataBase[Date]<=EARLIER(CompanyPortalDataBase[Date])))

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    This is the calculated column i used in the CompanyPortalDataBase table

     

    =CALCULATE(SUM(CompanyPortalDataBase[PageVisit]),FILTER(CompanyPortalDataBase,CompanyPortalDataBase[Category]=EARLIER(CompanyPortalDataBase[Category])&&CompanyPortalDataBase[Year]=EARLIER(CompanyPortalDataBase[Year])&&CompanyPortalDataBase[Date]<=EARLIER(CompanyPortalDataBase[Date])))

  • nhol's avatar
    nhol
    Advocate II
    8 years ago

    Hi Ashish,

     

    I just had a chance to test your formula and it worked just fine, Thanks!! Not sure what I did wrong in the first place that didn't work for me. Maybe I should have thought about column instead of a new measure.

    Anyway, the next step is a acummulated running average.

    What needs to be added in order to get this function to work?

     

    Best,

    Nir H.

  • nhol's avatar
    nhol
    Advocate II
    8 years ago

    Perfect!!!

     

    Thank you Ashish.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    You are welcome.