DAX Forecast

[ivanajoh]

Hi,

Can anyone help me with tips or logic how to show up my Last 1 Year Ago, Last 2 Year Ago, and Forecast in the red square that not show up?

Dax that I used:
Year

{M} Trends_WoW_1_Year =

var param_date = max(sales_details[delivery_date])

var param_year =

LOOKUPVALUE(

calender[Year of Week Number],

calender[Dates],

param_date

)

Return

param_year

Week Number

{M} Trends_WoW_WeekNum =

var param_date = max(calender[Dates])

var param_week =

LOOKUPVALUE(

calender[Week Number],

calender[Dates],

param_date

)

Return

param_week

Current Year

{M} Trends_WoW_1_Year_Total =

var param_weeknum = [{M} Trends_WoW_WeekNum]

var param_year= [{M} Trends_WoW_1_Year]

return

CALCULATE(

sum(sales_details[price_net]),

filter(

all(calender),

calender[Year of Week Number] = param_year &&

calender[Week Number] = param_weeknum

)

)

Last 1 Year Ago

{M} Trends_WoW_2_Year_Total =

var param_weeknum = [{M} Trends_WoW_WeekNum]

var param_year= [{M} Trends_WoW_2_Year]

return

CALCULATE(

sum(sales_details[price_net]),

filter(

all(calender),

calender[Year of Week Number] = param_year &&

calender[Week Number] = param_weeknum

)

)

Last 2 Year Ago

{M} Trends_WoW_3_Year_Total =

var param_date = [{M} salesData_latestDate]

var param_weeknum = [{M} Trends_WoW_WeekNum]

var param_year= [{M} Trends_WoW_3_Year]

return

CALCULATE(

sum(sales_details[price_net]),

filter(

ALL(calender),

calender[Year of Week Number] = param_year &&

calender[Week Number] = param_weeknum

)

)

Thank you

[lbendlin]

Assuming your Calender table is a true Dates table (contiguous, covering, marked as Dates table) you can use standard Time Intelligence functions like SAMEPERIODLASTYEAR or DATEADD.  That should make your code a bit more concise.