Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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 , 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
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |