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 formula multi dimensional model trying convert mdx formula dax formula use in tubular model.
12 month avg := if ( calculate ( sum ( [qty] ), filter ( ( calendar[month] ), calendar[month] - 11 = ( calendar[month] - 11 ) ) ) = 0, blank (), calculate ( sum ( [num] ), filter ( ( calendar[month] ), calendar[month] - 11 = ( calendar[month] - 11 ) ) ) / calculate ( sum ( [qty] ), filter ( ( calendar[month] ), calendar[month] - 11 = ( calendar[month] - 11 ) ) ) ) so made dax formula convert mdx formula @ top. seem not working when select month in pivot table. 2 formula don't match when filtered month. how can resolve problem?
1) create 3 base measures:
totalnum := sum([num]) totalqty := sum([qty]) avg := divide ( [totalnum], [totalqty], 0 ) 2) create calculated measure calculate average on prior year including current selected month:
avglastyear:= calculate ( [avg] , datesinperiod ( calendar[date] , max(calendar[date]), -1, year ) ) explanation:
first, don't need divide 0 rigmarole, both mdx , dax have divide() function handles implicitly.
second, dax, idea build base measure , use calculate() shift context of measure needed - in case time period.
here we're looking @ current selected month (represented max(calendar[date]), though use aggregation function) , using datesinperiod() choose set of dates in our calendar table represent time period t-1 year current month.
Comments
Post a Comment