Running Total

[Atif]

The following formulae are displaying each month's respective figure rather than a running total.

"YTD Revenue = CALCULATE(SUM(Table1[Revenue]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))"

"LY YTD = CALCULATE([YTD Revenue], SAMEPERIODLASTYEAR('Calendar'[Date]))"

To calculate YoY difference and variance I am using the following:

Difference = [YTD Revenue]-[LY YTD]

Variance % = DIVIDE([Difference],[LY YTD])

A waterfall chart is being used to display the difference, but with above formula I am only getting total revenue of FY 2018.

The formula for variance is displaying previous months' headings with no value.

[v-yulgu-msft]

Hi @Atif,

Do you want to use a Waterfall chart to display YOY difference or running total? Could please share some sample data and show us the current result you have gotten?

Regards,

Yuliana Gu

[Atif]

@v-yulgu-msft 

The waterfall chart is not a must. I am interested in:

• Running Total;
• YoY Difference; and
• YoY Variance

The option of having a measure each for "YTD Total", "LY YTD", "Difference" and "Variance" will save me from creating 3 separate measures for each year i.e., "FY1

A sample of my data is as under with a lot of hidden columns.

Day                 Revenue               Station            BU            Sub Category                       Paper       Size              Stations    Date

Weekday	5,100	Lahore	DJL	Back Page Panel	Jang	B (6-10)	L	Dec/1/2011
Weekday	3,825	Lahore	DJL	ROP - Announcement	Jang	B (6-10)	L	Dec/1/2011
Sunday	3,953	Lahore	DJL	ROP - Small Size	Jang	A( 1-5)	L	Nov/1/2011
Weekday	2,550	Multan	DJM	ROP - Small Size	Jang	B (6-10)	M	Jul/1/2011
Weekday	2,550	Multan	DJM	ROP - Small Size	Jang	B (6-10)	M	Aug/1/2011
Weekday	3,366	Rawalpindi	DJR	ROP - Small Size	Jang	B (6-10)	R	Jul/1/2011
Sunday	5,387	Karachi	TNK	ROP - Small Size	News	B (6-10)	K	Jul/1/2011
Sunday	76,886	Rawalpindi	DJR	ROP - Prime Display	Jang	G (Other)	R	Jul/1/2011
Sunday	10,816	Rawalpindi	DJR	ROP - Small Size	Jang	B (6-10)	LR	Jul/1/2011
Sunday	12,996	Rawalpindi	DJR	ROP - Small Size	Jang	B (6-10)	R	Jul/1/2011
Sunday	77,976	Rawalpindi	DJR	ROP - Small Size	Jang	B (6-10)	R	Jul/1/2011
Weekday	22,572	Rawalpindi	DJR	ROP - Small Size	Jang	B (6-10)	R	Jul/1/2011
Sunday	3,249	Rawalpindi	DJR	ROP - Small Size	Jang	A( 1-5)	R	Jul/1/2011
Sunday	10,816	Rawalpindi	DJR	ROP - Small Size	Jang	B (6-10)	LR	Jul/1/2011
Sunday

The data against "Difference" is something like this:

Difference	Fiscal Year	Comment
xxxxxxxxxx	FY18	The exact amount is being displayed for current fiscal year
0	FY12
0	FY13
0	FY14
0	FY15
0	FY16
0	FY17

The data against "Variance" is bringing no value

Variance %	Fiscal Year
0	FY12
0	FY13
0	FY14
0	FY15
0	FY16
0	FY17

[Anonymous]

Hi @Atif

Just to give an idea you can make use of quick measures it may help you out. refer the image below

[Atif]

Hi there @Anonymous

Thanks a bunch!

The quick measure "Running Total" is displaying respective month's figures without adding up in following months.

Moreover, I need running total based on Fiscal Year (July to June).

The result of quick measure "Year-to-date total" is blank report.

[Anonymous]

Hi  @Atif

Rolling total working good for me can you check once again and if its possible can show some ref images 

[Atif]

@Anonymous

To avoid any error being carry forwarded, I connected a fresh set of data (fictitious), yet nothing happened with "quick measures".

The result of the two measures "Year-to-date total" and "Running total" is as under.

Month Name	FY12	FY13	FY14	FY15	FY16	FY17	FY18	Total
July	302	332	378	393	408	438	453	2,703
August	302	332	378	393	408	438	453	2,703
September	302	332	378	393	408	438	453	2,703
October	302	332	378	393	408	438	453	2,703
November	302	332	378	393	408	438	453	2,703
December	302	332	378	393	408	438	453	2,703
January	302	332	378	393	408	438	-	2,250
February	302	332	378	393	408	438	-	2,250
March	302	332	378	393	408	438	-	2,250
April	302	332	378	393	408	438	-	2,250
May	302	332	378	393	408	438	-	2,250
June	302	332	378	393	408	438	-	2,250
Total	3,624	3,986	4,530	4,711	4,892	5,255	2,718	29,717

The result of other three measures "Year-over-year change", "Month-over-month change" and "Percentage difference" is:

Month Name	FY12	FY13	FY14	FY15	FY16	FY17	FY18	Total
July	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
August	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
September	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
October	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
November	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
December	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
January	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
February	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
March	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
April	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
May	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
June	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
Total	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%

Please note that I have tried to work with both "calendar year" and "fiscal year".

All I need now is a measure for:

- YTD Revenue;

- YTD Revenue for previous years;

- YoY Difference; and

- YoY Variance