Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Difference 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 ...
  • Irwan's avatar
    Irwan
    2 years ago

    Hello Anonymous 

     

    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.

Irwan's avatar
Irwan
Icon for Super User rankSuper User
2 years ago

Hello Anonymous 

 

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.