Forum Discussion

kvonck34's avatar
kvonck34
Frequent Visitor
5 years ago
Solved

Cumulative Measures Using Monthly Buckets.

Hello,

 

I am trying to create a visual that shows a cumulative forecast variance throughout the year.  I'd like to show the cumulative absolute value of the forecast v actual variance by month.  I think my issue is the by month.  I've created two measures:

 

R&A Absolute Delta = abs(sum('Import - RA Financials'[Actual Value])-sum('Import - RA Financials'[Forecast Value]))
 
Cum_Absolute Value =
SUMX (
FILTER (
ALLSELECTED ('Import - RA Financials'),
'Import - RA Financials'[R&A Period Date]<=max('Import - RA Financials'[R&A Period Date])
),
[R&A Absolute Delta]
)
 
I get the below result:
 
 
 
Period DateForecast ValueActual ValueR&A Absolute DeltaCum_Absolute Value
1/1/2020       3,091,234       1,591,230                 1,500,004          5,151,816
2/1/2020       3,640,461       2,852,832                     787,629        12,118,687
3/1/2020       2,526,878       1,108,853                 1,418,025        16,437,166
4/1/2020       1,633,300       1,416,034                     217,266        19,582,738
5/1/2020       1,059,937           885,944                     173,993        21,568,999
6/1/2020           996,816           922,700                       74,116        23,781,483
7/1/2020       3,416,619       2,794,734                     621,885        30,167,504
8/1/2020       3,299,384       2,047,704                 1,251,680        35,532,458
9/1/2020       3,662,643                     93                 3,662,550        39,197,638
10/1/2020       3,420,711                     85                 3,420,626        42,618,434
11/1/2020       2,948,893                     61                 2,948,832        45,567,388
12/1/2020       2,265,416                     51                 2,265,365        48,632,567
 
 

 

 

As you can see, the cumultaive value is not the same as the simple addition of the Period Date columns.  I suspect it has something to do with my R&A Absolute Delta measure completing the absolute value in monthly buckets vs my cumulative measure completing them on each individual row of data so the totals are off, but i'm not sure.

 

Can someone help me understand how to get this fixed?

 

Thanks,

 

Kyle 

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    kvonck34 , Do not use abs in

    R&A Absolute Delta = sum('Import - RA Financials'[Actual Value])-sum('Import - RA Financials'[Forecast Value])

     

    if needed used it in Cum_Absolute Value

     

    Cum_Absolute Value =
    abs(SUMX (
    FILTER (
    ALLSELECTED ('Import - RA Financials'),
    'Import - RA Financials'[R&A Period Date]<=max('Import - RA Financials'[R&A Period Date])
    ),
    [R&A Absolute Delta]
    ))

    • kvonck34's avatar
      kvonck34
      Frequent Visitor
      5 years ago

       

       

       
      R&A Period DateForecast ValueActual ValueR&A Absolute DeltaCum_Absolute Value
      1/1/2020 0:00       3,091,234       1,591,230       (1,500,004)                  (1,500,004)
      2/1/2020 0:00       3,640,461       2,852,832           (787,629)                  (2,287,633)
      3/1/2020 0:00       2,526,878       1,108,853       (1,418,025)                  (3,705,658)
      4/1/2020 0:00       1,633,300       1,416,034           (217,266)                  (3,922,924)
      5/1/2020 0:00       1,059,937           885,944           (173,993)                  (4,096,917)
      6/1/2020 0:00           996,816           922,700             (74,116)                  (4,171,033)
      7/1/2020 0:00       3,416,619       2,794,734           (621,885)                  (4,792,918)
      8/1/2020 0:00       3,299,384       2,047,704       (1,251,680)                  (6,044,598)
      9/1/2020 0:00       3,662,643                     93       (3,662,550)                  (9,707,148)
      10/1/2020 0:00       3,420,711                     85       (3,420,626)                (13,127,774)
      11/1/2020 0:00       2,948,893                     61       (2,948,832)                (16,076,606)
      12/1/2020 0:00       2,265,416                     51       (2,265,365)                (18,341,971)

       

      Also, is there a way within the formula to only track the cumulative delta on days prior to today?