Forum Discussion

MarkCBB's avatar
MarkCBB
Icon for Helper V rankHelper V
9 years ago

DAX Calculated Column for previous X months

Hi there,

 

I am more of an excel user than DAX, so I am pretty sure a calculated column is not the way to go, but for my understand to grow I would like to see the result in a calculated column before I move this into a Measure.

 

I have a simple fact table that contains the following:

Date 

Store ID

Product ID

Sales

 

I would like to add a calculated column to calculate the previous 3 months (Including the current month until the relivate date). aveage sales (Product & Site)

 

obviously when starting on the first date (i.e. 2016-01-01 there is no history so I am assuming that the result for that row would be the same as the acutaly sales.

 

Hope the above makes sence.

 

note, the table has 7m rows, the average sales works as a "Helper column to calculate another column.

 

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi MarkCBB

     

    Can you provide some data and the output expected.

     

    Cheers

     

    CheenuSing

    • Thiyags's avatar
      Thiyags
      Icon for Helper II rankHelper II
      9 years ago

      MarkCBB

       

      You can create a measure with the following formula

       

      RT Last 3 Months Sales =
      CALCULATE (
      SUM(Sheet[Sales]),
      DATESINPERIOD (
      Sheet[Date].[Date],
      LASTDATE ( Sheet[Date].[Date] ),
      -3,
      MONTH
      )
      )

       

      In this i have stored the data in Sheet.