Difference between values for selected slicer date and previous month

[metcala]

Hi

I have the following table.

Month	Project	Week Commencing	Role	Hours
1/1/24	A	1/4/24	Analyst	10
1/1/24	A	1/4/24	Project Manager	5
1/1/24	B	1/4/24	Analyst	25
1/1/24	B	1/4/24	Project Manager	20
1/1/24	A	8/4/24	Analyst	10
1/1/24	A	8/4/24	Project Manager	5
1/1/24	B	8/4/24	Analyst	25
1/1/24	B	8/4/24	Project Manager	20
1/2/24	A	1/4/24	Analyst	15
1/2/24	A	1/4/24	Project Manager	10
1/2/24	B	1/4/24	Analyst	20
1/2/24	B	1/4/24	Project Manager	20
1/2/24	A	8/4/24	Analyst	15
1/2/24	A	8/4/24	Project Manager	5
1/2/24	B	8/4/24	Analyst	25
1/2/24	B	8/4/24	Project Manager	25

I am looking to get the following results showing the difference from the month selected vs the previous month

Slicer: Month e.g. 1/2

Project	Role	1/4/24	8/4/24
A	Analyst	5	5
A	Project Manager	5	0
B	Analyst	-5	0
B	Project Manager	0	5

This is my current thinking but just can't seem to get it working....

Month Difference = 
VAR SelectedMonth = MAX('Supply'[Month])
VAR PriorMonth = EOMONTH(SelectedMonth,-2)+1
VAR SupplySelectedMonth =
   CALCULATE(
      SUM('Supply'[Hours]),
      'Supply'[Month] = SelectedMonth
   )
VAR SupplyPriorMonth =
   CALCULATE(
      SUM('Supply'[Hours]),
      'Supply'[Month] = PriorMonth
   )
RETURN
IF(
   ISBLANK(SupplySelectedMonth),
   BLANK(),
   SupplySelectedMonth - SupplyPriorMonth
)

Any help would be much appreciated!

[Irwan · Accepted solution]

Hello @metcala 

please check if this acomodate your need.

Create new measure as following DAX

Month Difference =

var _SelectedMonth = SELECTEDVALUE('Supply'[Month]) //find selected date

var _PriorMonth = EOMONTH(_SelectedMonth,-1) //define previous date of selected date

var _SupplySelectedMonth = CALCULATE(SUM('Supply'[Hours]),EOMONTH('Supply'[Month],0)=EOMONTH(_SelectedMonth,0)) //calculate sum of selected date

var _SupplyPriorMonth = CALCULATE(SUM('Supply'[Hours]),EOMONTH('Supply'[Month],-1)=EOMONTH(_PriorMonth,-1)) //calculate sum of previous date

Return

IF(

    ISBLANK(_SupplySelectedMonth),

    BLANK(),

    _SupplySelectedMonth-_SupplyPriorMonth

)

From your DAX, it looks like the _SelectedMonth will be always maximum date since you are using MAX('Supply'[Month]), but you want to see value difference from any selected date (not only maximum date). However if my assumption is wrong, then please adjust the DAX into your preference SelectedMonth.

Hope this will help you.

Thank you.