cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

Running Total

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.

6 REPLIES 6
Super Contributor

Re: Running Total

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Re: Running Total

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

Frequent Visitor

Re: Running Total

Hi @Atif

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

Regular Visitor

Re: Running Total

Hi there @vigneshmani05

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.

Frequent Visitor

Re: Running Total

Hi  @Atif

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

Regular Visitor

Re: Running Total

@vigneshmani05

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

Helpful resources

Announcements

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 263 members 3,180 guests
Recent signins:
Please welcome our newest community members: