Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
v-yiruan-msft
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
v-yiruan-msft
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 @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:

 

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.