Forum Discussion
Running Total
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 YuHi,
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])))
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.
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.
Hi,
I cannot comment unless i see your file. Tell me exactly where my result deviates from your expected result.
Hi,
Per your request, hereby the link to my model.
Thanks!
NH
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])))