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.
Hi Guys,
I have the following measure:
BUT, I now need to add the 'previous month' only count into the measure, so that I can add it to a static scorecard on another report
Date Complete field: DATE-COMP
Please can you help, tried PREVIOUSMONTH, MONTH etc and now can't see the wood from the trees, any help much appreciated.
Solved! Go to Solution.
Hi @StevenHarrison ,
If I understand correctly, you would like to get the values in previous month of selected [Date Complete]. If the field [Date Complete] is from another date dimension table 'Date' , you can create a measure as below to get it:
GASS Service Jobs Count Previous Month =
VAR _selyear =
SELECTEDVALUE ( 'Date'[Date Complete].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'Date'[Date Complete].[MonthNo] )
VAR _pmedate =
EOMONTH ( DATE ( _selyear, _selmonth, 1 ), -1 )
RETURN
CALCULATE (
[Jobs Count],
FILTER (
ALLSELECTED ( 'RM-JOB - with Address and VAT Calcs' ),
'RM-JOB - with Address and VAT Calcs'[JOB-TYPE] = "GASS"
&& 'RM-JOB - with Address and VAT Calcs'[STD-JOB-CODE] <> "NEWB"
&& 'RM-JOB - with Address and VAT Calcs'[CONTRACTOR] = "PAC01"
&& YEAR ( 'RM-JOB - with Address and VAT Calcs'[Job Date] ) = YEAR ( _pmedate )
&& MONTH ( 'RM-JOB - with Address and VAT Calcs'[Job Date] = MONTH ( _pmedate ) )
)
)
If the above one can't help you get the desired result, please provide some sample data in your table 'RM-JOB - with Address and VAT Calcs' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. By the way, is the field [Date Complete] also from the table'RM-JOB - with Address and VAT Calcs'? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @StevenHarrison ,
If I understand correctly, you would like to get the values in previous month of selected [Date Complete]. If the field [Date Complete] is from another date dimension table 'Date' , you can create a measure as below to get it:
GASS Service Jobs Count Previous Month =
VAR _selyear =
SELECTEDVALUE ( 'Date'[Date Complete].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'Date'[Date Complete].[MonthNo] )
VAR _pmedate =
EOMONTH ( DATE ( _selyear, _selmonth, 1 ), -1 )
RETURN
CALCULATE (
[Jobs Count],
FILTER (
ALLSELECTED ( 'RM-JOB - with Address and VAT Calcs' ),
'RM-JOB - with Address and VAT Calcs'[JOB-TYPE] = "GASS"
&& 'RM-JOB - with Address and VAT Calcs'[STD-JOB-CODE] <> "NEWB"
&& 'RM-JOB - with Address and VAT Calcs'[CONTRACTOR] = "PAC01"
&& YEAR ( 'RM-JOB - with Address and VAT Calcs'[Job Date] ) = YEAR ( _pmedate )
&& MONTH ( 'RM-JOB - with Address and VAT Calcs'[Job Date] = MONTH ( _pmedate ) )
)
)
If the above one can't help you get the desired result, please provide some sample data in your table 'RM-JOB - with Address and VAT Calcs' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. By the way, is the field [Date Complete] also from the table'RM-JOB - with Address and VAT Calcs'? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @v-yiruan-msft - yes the Date Complete field (DATE-COMP) is from the same table, so using your code:
This works more cleanly than what I had come up with, so thanks for your help and I get the result needed 🙂
GASS Service Jobs Count Previous Month =
VAR _selyear =
SELECTEDVALUE ( 'RM-JOB - with Address and VAT Calcs'[DATE-COMP].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'RM-JOB - with Address and VAT Calcs'[DATE-COMP].[MonthNo] )
VAR _pmedate =
EOMONTH ( DATE ( _selyear, _selmonth, 1 ), -1 )
RETURN
CALCULATE (
[Jobs Count],
FILTER (
ALLSELECTED ( 'RM-JOB - with Address and VAT Calcs' ),
'RM-JOB - with Address and VAT Calcs'[JOB-TYPE] = "GASS"
&& 'RM-JOB - with Address and VAT Calcs'[STD-JOB-CODE] <> "NEWB"
&& 'RM-JOB - with Address and VAT Calcs'[CONTRACTOR] = "PAC01"
&& YEAR ( 'RM-JOB - with Address and VAT Calcs'[DATE-COMP] ) = YEAR ( _pmedate )
&& MONTH ( 'RM-JOB - with Address and VAT Calcs'[DATE-COMP] = MONTH ( _pmedate ) )
)
)
Hi Guys - came up with the following, is there a better way of doing this? I added 2 columns to the table Job Month and Job Year, the DAX looks like this:
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 |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |