Forum Discussion
Helper IIIDifference between values for selected slicer date and previous month
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!
Hello metcala
please check if this acomodate your need.
Create new measure as following DAX
Month Difference =var _SelectedMonth = SELECTEDVALUE('Supply'[Month]) //find selected datevar _PriorMonth = EOMONTH(_SelectedMonth,-1) //define previous date of selected datevar _SupplySelectedMonth = CALCULATE(SUM('Supply'[Hours]),EOMONTH('Supply'[Month],0)=EOMONTH(_SelectedMonth,0)) //calculate sum of selected datevar _SupplyPriorMonth = CALCULATE(SUM('Supply'[Hours]),EOMONTH('Supply'[Month],-1)=EOMONTH(_PriorMonth,-1)) //calculate sum of previous dateReturnIF(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.
1 Reply
IrwanSuper User
Hello metcala
please check if this acomodate your need.
Create new measure as following DAX
Month Difference =var _SelectedMonth = SELECTEDVALUE('Supply'[Month]) //find selected datevar _PriorMonth = EOMONTH(_SelectedMonth,-1) //define previous date of selected datevar _SupplySelectedMonth = CALCULATE(SUM('Supply'[Hours]),EOMONTH('Supply'[Month],0)=EOMONTH(_SelectedMonth,0)) //calculate sum of selected datevar _SupplyPriorMonth = CALCULATE(SUM('Supply'[Hours]),EOMONTH('Supply'[Month],-1)=EOMONTH(_PriorMonth,-1)) //calculate sum of previous dateReturnIF(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.