cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Atif Regular Visitor
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
v-yulgu-msft Super Contributor
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.
Atif Regular Visitor
Regular Visitor

Re: Running Total

@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,100LahoreDJLBack Page PanelJangB (6-10)LDec/1/2011
Weekday             3,825LahoreDJLROP - AnnouncementJangB (6-10)LDec/1/2011
Sunday             3,953LahoreDJLROP - Small SizeJangA( 1-5)LNov/1/2011
Weekday             2,550MultanDJMROP - Small SizeJangB (6-10)MJul/1/2011
Weekday             2,550MultanDJMROP - Small SizeJangB (6-10)MAug/1/2011
Weekday             3,366RawalpindiDJRROP - Small SizeJangB (6-10)RJul/1/2011
Sunday             5,387KarachiTNKROP - Small SizeNewsB (6-10)KJul/1/2011
Sunday           76,886RawalpindiDJRROP - Prime DisplayJangG (Other)RJul/1/2011
Sunday           10,816RawalpindiDJRROP - Small SizeJangB (6-10)LRJul/1/2011
Sunday           12,996RawalpindiDJRROP - Small SizeJangB (6-10)RJul/1/2011
Sunday           77,976RawalpindiDJRROP - Small SizeJangB (6-10)RJul/1/2011
Weekday           22,572RawalpindiDJRROP - Small SizeJangB (6-10)RJul/1/2011
Sunday             3,249RawalpindiDJRROP - Small SizeJangA( 1-5)RJul/1/2011
Sunday           10,816RawalpindiDJRROP - Small SizeJangB (6-10)LRJul/1/2011
Sunday

 

The data against "Difference" is something like this:

 

DifferenceFiscal YearComment
xxxxxxxxxxFY18The exact amount is being displayed for current fiscal year
0FY12 
0FY13 
0FY14 
0FY15 
0FY16 
0FY17 

 

The data against "Variance" is bringing no value

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

 

vigneshmani05 Frequent Visitor
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 belowQuick measures.jpg

 

 

Atif Regular Visitor
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.

vigneshmani05 Frequent Visitor
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 

Atif Regular Visitor
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 NameFY12FY13FY14FY15FY16FY17FY18Total
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 NameFY12FY13FY14FY15FY16FY17FY18Total
July0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
August0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
September0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
October0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
November0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
December0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
January0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
February0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
March0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
April0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
May0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
June0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
Total0.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
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 263 members 3,180 guests
Please welcome our newest community members: