Previous Month issue with measure

[StevenHarrison]

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: 

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.

[yingyinr · Accepted 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

[StevenHarrison]

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 ) )
)
)

[StevenHarrison]

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

        )