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

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
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 

nhol's avatar
nhol
Advocate II
8 years ago

This worked just fine... Thank you!

Now I have another challenge that I should incorporate into this measure.

I have another filter which is a text category filter that needs to be taken into consideration otherwise the running total summarize the entire population by year and ignores the category.

The category name is: WebCat

And the values are:

  • HomePage
  • CompanyPage
  • ProductPage
  • AboutPage

 

How can this be incorporated?

 

Thanks in advance!

NirH