cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Previous Month issue with measure

Hi Guys,

I have the following measure:

 
GASS Service Jobs Count Previous Month = CALCULATE(
    [Jobs Count],
    'RM-JOB - with Address and VAT Calcs'[JOB-TYPE] IN {"GASS"},
    'RM-JOB - with Address and VAT Calcs'[STD-JOB-CODE] <> {"NEWB"},
    'RM-JOB - with Address and VAT Calcs'[CONTRACTOR] IN {"PAC01"}    
)
I have a 'Date Complete' Slicer that does give me the values I need: 
date slicer.jpg

 


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

StevenHarrison_2-1670497865484.png

Please can you help, tried PREVIOUSMONTH, MONTH etc and now can't see the wood from the trees, any help much appreciated.

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @yingyinr  - 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:

 

GASS Service Jobs Count Previous Month =

var _PMonth = MONTH(TODAY())-1
var _PYear = YEAR(TODAY())

return

CALCULATE(
    [Jobs Count],
    'RM-JOB - with Address and VAT Calcs'[JOB-TYPE] IN {"GASS"},
    'RM-JOB - with Address and VAT Calcs'[STD-JOB-CODE] <> {"NEWB"},
    'RM-JOB - with Address and VAT Calcs'[CONTRACTOR] IN {"PAC01"},
    'RM-JOB - with Address and VAT Calcs'[Job Month] = _PMonth,
    'RM-JOB - with Address and VAT Calcs'[Job Year] = _PYear
        )

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.