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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Share some data and show the expected result.

      • nhol's avatar
        nhol
        Advocate II
        8 years ago

        Hi Ashish,

         

        Basically I’m trying to achieve the last two columns marked in blue and red.

        My database is much larger than the screenshot attached and it has others WebCat and also 2017 data. The date remains a monthly data (I didn't know how to share my PBI here so if needed I can do that as well but I will need some instructions).

         

        I want to let my users the ability to drill through from one summary report to a report that show much detail information including this running total and most importantly running average on a monthly basis while filtered by [Year] AND [WebCat] which are the drill through parameters.

         

        What DAX formula should I’ll be using to implement this.

         

         

         

        Thanks!

        Nir H.