CODEWITHSUNDEEP
excelmdxpivot-tabledaxtabular
HAIZD
HAIZD

Reputation: 179

how to get a year history of data in dax

IIF(SUM
 (
 [Calendar].[Month].CurrentMember.Lag(11) :
 [Calendar].[Month].CurrentMember,
 [Measures].[Qty]
 ) = 0, 0,
SUM
 (
 [Calendar].[Month].CurrentMember.Lag(11) :
 [Calendar].[Month].CurrentMember,
 [Measures].[Num]
 ) /

SUM
 (
 [Calendar].[Month].CurrentMember.Lag(11) :
 [Calendar].[Month].CurrentMember,
 [Measures].[Qty]
 ) )

This is formula from multi dimensional model i am trying to convert this MDX formula to DAX formula to use in Tubular model.

12 Month Avg  :=
IF (
    CALCULATE (
        SUM ( [QTY] ),
        FILTER (
            ALL ( Calendar[Month] ),
            Calendar[Month] - 11
                = ( Calendar[Month] - 11 )
        )
    )
        = 0,
    BLANK (),
    CALCULATE (
        SUM ( [Num] ),
        FILTER (
            ALL ( Calendar[Month] ),
            Calendar[Month] - 11
                = ( Calendar[Month] - 11 )
        )
    )
        / CALCULATE (
            SUM ( [QTY] ),
            FILTER (
                ALL ( Calendar[Month] ),
                Calendar[Month] - 11
                    = ( Calendar[Month] - 11 )
            )
        )
)

So i made this DAX formula to convert MDX formula at the top. However it seem not working properly when i select month in pivot table. Those two formula don't match when i filtered by month. How can i resolve that problem?

Upvotes: 0

Views: 291

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

1) Create three base measures:

TotalNum := SUM([Num])

TotalQty := SUM([Qty])

Avg := DIVIDE ( [TotalNum], [TotalQty], 0 )

2) Create a calculated measure to calculate the average over the prior year including the current selected month:

AvgLastYear:= CALCULATE ( 
   [Avg] , 
   DATESINPERIOD ( 
       Calendar[Date] , 
       MAX(Calendar[Date]),
       -1, year
   ) 
)

Explanation:

First, you don't need that divide by zero rigmarole, both MDX and DAX have a DIVIDE() function which handles that implicitly.

Second, with DAX, the idea is to build a base measure and then use CALCULATE() to shift the context of that measure as needed - in this case the time period.

Here we're looking at the current selected month (represented as MAX(Calendar[Date]), though you could use any aggregation function) and then using DATESINPERIOD() to choose a set of dates in our Calendar table which represent the time period T-1 year to the current month.

Upvotes: 2

Related Questions