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
        )
    )
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

I cannot understand anything there at all.  Share an MS Excel file with your Excel formulas.  I will convert those Excel formulas into DAX measures.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Ashish, unable to add excel here but can paste values frome excel as below.

for Nov , =SUM(C2:C13)-C2

for dec  =SUM(C3:C13)-C3

for Jan, =SUM(C4:C14)-C4

and so on

 

Appreciate your help here,,

 

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