Display the latest MoM & YoY difference and percentage for selected Country

[bammyd]

Hello.

Please see the Sample_Table below.

Using Country as the slicer, I want to create a measure that displays the latest MoM & YoY variance on a Card (i.e. Total_Cost - Previous_Mth). I also have a Calendar table.

Sample_Table

Country	Code	Price	Time series	Year
USA	USA	550	01-Dec-23	2023
Canada	CAN	310	01-Dec-23	2023
Mexico	MEX	420	01-Dec-23	2023
Canada	CAN	620	01-Jan-24	2024
Mexico	MEX	400	01-Jan-24	2024
USA	USA	460	01-Jan-24	2024
Canada	CAN	380	01-Feb-24	2024
USA	USA	600	01-Feb-24	2024
Mexico	MEX	530	01-Feb-24	2024
Canada	CAN	450	01-Mar-24	2024
USA	USA	465	01-Mar-24	2024
Mexico	MEX	550	01-Mar-24	2024

I have the following measures.

Total_Cost = SUM(SampleTable[Price])

Previous_Mth = CALCULATE ([TotalCost], PREVIOUSMONTH (Calendar[Date]))

MoM_Variance = [Total_Cost] - [Previous_Mth]

Latest_MoM_Variance =

 CALCULATE([MoM_Variance]),

    FILTER(

        SampleTable,

          Sample_Table[Time series] = MAX(SampleTable[Time series])

    ))

The measures above did not display the correct value, I want to be able to select Canada on the slicer and see the MOM & YoY variance for 01-Mar-2024.

Thank you.

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @amitchandak , please allow me to provide another insight: 
Hi  @bammyd ,

You can try the following DAX:

MoM_Variance =
var _select=SELECTEDVALUE('Table'[Country])
var _Total_Cost=SUM('Table'[Price])
var _today=TODAY()
var _Previous_Mth=
SUMX(FILTER(ALL('Table'),'Table'[Country]=_select&&'Table'[Time series]>EOMONTH(_today,-2)&&'Table'[Time series]<=EOMONTH(_today,-1)),[Price])
return
_Total_Cost-_Previous_Mth

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bammyd]

Thank you for your response @v-yangliu-msft but the result I want is to see the latest MoM & YoY difference which is in March 2024. So for example; when I select USA the value will be -135, for Mexico = 20 and Canada = 70. Hope this clarifies things a bit. thank you.

[amitchandak]

@bammyd , Try measures like, with help from date/calendar table

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([MoM_Variance] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([MoM_Variance] ,DATESBETWEEN('Date'[Date],_min,_max))

MTD if needed

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

[bammyd]

Thank you for your response @amitchandak  but what I want is Country as the Slicer so when ISFILTERED Country, I want the card to display the MoM_Variance value for 1 Mar 2024 for the selected country. Thank you