Running Total

[nhol]

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 (
ALL ( CompanyPortalDataBase),
'CompanyPortalDataBase'[Date] <= MAX ( 'CompanyPortalDataBase'[Date] )
)
)

The issue is when I'm filtering my report using a slicer on 2016 the measure works fine and it does bring 2016 running total month by month. However, when I'm selecting 2017, the measure calculates 2 years in a row which mean that January 2017 already includes the total of 2016 and it keeps accumulating from that point. This overstated the 2017 numbers.

I need to present each year running total separately.

Thanks!

NH

[v-qiuyu-msft · Accepted solution]

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 

[v-qiuyu-msft · Accepted solution]

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]

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]

Hi,

Share some data and show the expected result.

[nhol]

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.

[Ashish_Mathur]

Hi,

Write this calculated column formula in the Query Editor window

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

This should get you the Running Total.  Let me know if this is correct and we will then get the Running Average.

Hope this helps.

[nhol]

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]

Hi,

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

[nhol]

Hi,

Per your request, hereby the link to my model.

https://nirh1153862-my.sharepoint.com/personal/nir_biteam_co_il/_layouts/15/guestaccess.aspx?docid=1...

Thanks!

NH

[Ashish_Mathur]

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]

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.

[Ashish_Mathur · Accepted solution]

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]

Perfect!!!

Thank you Ashish.

[Ashish_Mathur]

You are welcome.