I have this powerpivot table :
Fiscal Revenue_Value
Jan-12 761238
Feb-12 538234
Mar-12 503990
Apr-12 578607
May-12 794209
Jun-12 857749
Jul-12 683114
Aug-12 765772
Sep-12 638268
Oct-12 664654
Nov-12 677114
Dec-12 481902
Jan-13 717801
Feb-13 554196
Mar-13 467578
Apr-13 861125
May-13 547982
Jun-13 843557
Jul-13 804521
Aug-13 633815
Sep-13 903311
Oct-13 866296
I am trying to use a correct DAX formula of the 12 month average, but the formula is incorrect. This is what I need help with.
Also, my data starts at Jan 2012. So in the Mar 2012 line I would like to average Jan,Feb,Mar 2012 even though I don't have 12 months worth of data.
This DAX formula calculates correctly until I get the 13th month. I underlined what is correct and italicized what is incorrect.
DAX formula :
=calculate(CALCULATE (
SALES[Revenue_Value],
DATESBETWEEN (
SALES[Fiscal],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE (SALES[Fiscal] ) ) ),
LASTDATE (SALES[Fiscal])
)
)/(CALCULATE (
CALCULATE(COUNTROWS(VALUES(SALES[FY_Mnth])), Sales ),
DATESBETWEEN (
SALES[Fiscal],
SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE (SALES[Fiscal]))),
LASTDATE (SALES[Fiscal])
)
)))
Here is what I have now :
Fiscal Revenue_Value 12 mos av
Jan-12 761238 761238
Feb-12 538234 649736
Mar-12 503990 601154
Apr-12 578607 595517
May-12 794209 635256
Jun-12 857749 672338
Jul-12 683114 673877
Aug-12 765772 685364
Sep-12 638268 680131
Oct-12 664654 678584
Nov-12 677114 678450
Dec-12 481902 662071
Jan-13 717801 721888
Feb-13 554196 768071
Mar-13 467578 807036
Apr-13 861125 878796
May-13 547982 924461
Jun-13 843557 994758
Jul-13 804521 1061801
Aug-13 633815 1114619
Sep-13 903311 1189895
Oct-13 866296 1262086
Bookmarks