Forum Discussion

Atif's avatar
Atif
Resolver I
8 years ago

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(...
Need Help
Atif's avatar
Atif
Resolver I
8 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Atif

 

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

 

 

  • Atif's avatar
    Atif
    Resolver I
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi  Atif

       

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

      • Atif's avatar
        Atif
        Resolver I
        8 years ago

        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 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