Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Update cards based on slicer selection, current/selected month, 1 month before and 2 months before

Hello.

 

I am new to PBI, struggling and would appreciate some help. I have been searching and trying various approaches, and honestly the DAX is rubbish. and so far I am unsuccessful. 

I have provided links and an excerpt of data with my example pbix.

I would like to update the cards based on a slicer selection for a "month year".

With no selection the three cards display related detail for the current month, 1 month earlier and 2 months earlier. 

This is what I have with no slicer selection.

If I were to select April 2022 in the slicer, I would like the cards to show detail for April 2022, Mar 2022 and Feb 2022 respectively. The three cards for the month are updating. It is the the count of Incidents and Hazards for which I would appreciate some help.

 

Code: related to WHS

WHS Incidents Current Month = CALCULATE ( COUNTROWS(Incident),'Subject'[Subject affected] IN {"Worker", "Relative / Visitor"} , DATESMTD( DATEADD ( Incident[Incident date], 0, MONTH)))
WHS Incidents Last Month = 
VAR Lmonth = CALCULATE( COUNTROWS(Incident),'Subject'[Subject affected] IN {"Worker","Relative / Visitor"}, DATESMTD( DATEADD( Incident[Incident date], -1, MONTH)))
VAR selectedLmonth = SELECTEDVALUE(XlCalendar[Month Year])
VAR Month1 =  CALCULATE( COUNTROWS(Incident),'Subject'[Subject affected] IN {"Worker","Relative / Visitor"},DATESMTD( DATEADD( Incident[Incident date], -1, MONTH)))
VAR noselect = ISFILTERED(XlCalendar[Month Year]) //Check for list item selection

RETURN if(noselect=FALSE,Format(Lmonth,"0"),FORMAT(lmonth,"0"))
WHS Incidents Prior Month = CALCULATE( COUNTROWS( Incident),'Subject'[Subject affected] IN {"Worker", "Relative / Visitor"}, DATESMTD( DATEADD( Incident[Incident date], -2, MONTH)))

Tables and relationsships:

 

Is what I am attempting possible?

Inc excerpt PBI community question Inc table 

 

 

 

 

 

 

  • Anonymous , Try measures  like

     

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


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

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

     

    They will take max selected date or today

     

    You can check the switch code for more options

    https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

     

2 Replies

  • Anonymous , Try measures  like

     

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


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

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

     

    They will take max selected date or today

     

    You can check the switch code for more options

    https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hello and thank you Amit. 
      I apologise for my late reply.
      The DAX you proposed does precisely what I hoped to achieve.

      I have included the DAX in my report.

      I appreciate your taking the time to assist me (a novice).