Forum Discussion

maurcoll's avatar
maurcoll
Icon for Helper IV rankHelper IV
2 years ago
Solved

Rolling 12 month total

Hi I am trying to calculate the last rolling 12 months data that i can put into a line chart visual. It will need to show monthly going back for 12 months as per the below data. So for Sept 23 it w...
Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    A measure like the following might work for you...

    rollingTotalMeasure =
    var _rollingMonths =
    12
    var _maxDate =
    MAXX(
        ALL('Table'),
        [MonthYear]
    )
    Return
    CALCULATE(
        SUM('Table'[Total]),
        FILTER(
            ALL('Table'), 
            [MonthYear] <= MAX('Table'[MonthYear]) && DATEDIFF([MonthYear], _maxDate, MONTH) < _rollingMonths
        )
    )
Anonymous's avatar
Anonymous
Not applicable
2 years ago

hi Ashish_Mathur   thanks for ur help...However I could not read ur pbix due to latest verison of pbi desktop..Anyway I have sorted out that part and my runway by CF is working... 

 

Can you please help me to convert another excel formulas in DAX for runway calculation

Actual requiremnt is as below.

 

Runway by Burnrate for Nov = Total of current cost - Nov cost i.e.( C2:C13)-C2
Runway by Burnrate for Dec =Runway by Burnrate for Nov - burn rate of Nov i.e F2-E2
Runway by Burnrate for Jan =Runway by Burnrate for Dec - burn rate of Dec i.e F3-E3
and so on

 

(C:C ) means All rows of column "current cost". I taken that to take total of that column and minus current cost of Nov (C2)

 

Actual excel for "AAA" is as below but in main source file AAA,BBB,CCC are added as column "FI name" so runway and burn rate should work when we select AAA,BBB,CCC from slicer.

 

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I cannot and will not work with just an image.  Share the download link of the MS Excel file with your formulas already written there.