Search code examples
daxtabular

display 12 month history with dax in tabular model

12 Month Qty:=CALCULATE ( 
     [Qty],
   DATESINPERIOD ( 
       Calendar[Date] , 
       MAX(Calendar[Date]),
       -12, Month
   ) 
)

I am using that formula to get last 12 month of data but in the pivot table the result is same as the sum of the column. How can i fix that dax formula to see last 12 month of history?

Solution

  • Without knowing anything further about your model, it's hard to say what's going wrong, because with a basic implementation your measure works just fine. Where do you differ from the following?

    Qty:=SUM(FactQuantity[Quantity])

12MonthQty:=
CALCULATE(
    [Qty]
    ,DATESINPERIOD(
        DimDate[Date]
        ,MAX(DimDate[Date])
        ,-12
        ,MONTH
    )
)

    Sample Data, Pivot, Model Diagram:

    enter image description here