Help with Measures for MoM / YoY change

[Anonymous]

Hi, 

I have a table below with 4 measures. Two measures are not working as intended i.e. returning same result. These measures are comparing 'Product' spend at a National level.

Here's link to sample data file >> https://drive.google.com/file/d/1dKPjIVaUAC0Z5CMcUUxHIYVTMvC1STnc/view?usp=sharing

@johnt75 , @Jihwan_Kim appreciate any help please. Thanks.

National Spend - MoM Change = 

var var_Current_month = CALCULATE(SUM(TECT_monthly_product_RTO_ALL[Spend]) , FILTER( ALL(TECT_monthly_product_RTO_ALL),TECT_monthly_product_RTO_ALL[Date] = TECT_monthly_product_RTO_ALL[Max Date] && TECT_monthly_product_RTO_ALL[AIC_RTO] = "All NZ" ) )

var var_previous_month = CALCULATE(SUM(TECT_monthly_product_RTO_ALL[Spend]) , FILTER(ALL(TECT_monthly_product_RTO_ALL), DATEDIFF( TECT_monthly_product_RTO_ALL[Date] ,TECT_monthly_product_RTO_ALL[Max Date], MONTH ) = 1    && TECT_monthly_product_RTO_ALL[AIC_RTO] = "All NZ" ) )

var var_mom_change_text =  FORMAT( (var_Current_month / var_previous_month ) - 1  , "0.0%")

var var_mom_change =   (var_Current_month / var_previous_month ) - 1 

var var_output = IF(var_mom_change >= 0 , "▲"&var_mom_change_text, "▼"&var_mom_change_text )

return var_output

National Spend - YoY Change = 

var var_Current_month = CALCULATE(SUM(TECT_monthly_product_RTO_ALL[Spend]) , FILTER( ALL(TECT_monthly_product_RTO_ALL), TECT_monthly_product_RTO_ALL[Date] = TECT_monthly_product_RTO_ALL[Max Date] && TECT_monthly_product_RTO_ALL[AIC_RTO] = "All NZ" ))

var var_previous_month = CALCULATE(SUM(TECT_monthly_product_RTO_ALL[Spend]) , FILTER( ALL(TECT_monthly_product_RTO_ALL),  DATEDIFF( TECT_monthly_product_RTO_ALL[Date] ,TECT_monthly_product_RTO_ALL[Max Date], MONTH ) = 12   && TECT_monthly_product_RTO_ALL[AIC_RTO] = "All NZ") )

var var_mom_change_text =  FORMAT( (var_Current_month / var_previous_month ) - 1  , "0.0%")

var var_mom_change =   (var_Current_month / var_previous_month ) - 1 

var var_output = IF(var_mom_change >= 0 , "▲"&var_mom_change_text, "▼"&var_mom_change_text )

return var_output

[amitchandak · Accepted solution]

@Anonymous , when you have a date prefer to use date table and time intelligence

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

YTD

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

Thanks @amitchandak Will look at creating date table.