Help with prior month measure

[Fusilier2]

Hi,

Hopefully somebody can suggest a quick fix for this.

I currently have this measure:

Apprentices Current Month =

CALCULATE(

    SUM(HR_data[No. of Apprentice]),

    FILTER(

        HR_data,

        HR_data[Period]=MAX(HR_data[Period])

))

It returns the data for the latest month in my table.

I'm trying to create a similar measure that will always return the value for the previous month.

I've tried adding -1 to the end of the max statement:

Apprentices Current Month =

CALCULATE(

    SUM(HR_data[No. of Apprentice]),

    FILTER(

        HR_data,

        HR_data[Period]=MAX(HR_data[Period]-1)

But that gives me a 'MAX function only accepts a column reference as an argement' error.

Can anybody suggest what I should do to fix this?

Thanks

[pankajnamekar25]

Hello @Fusilier2 

You can try this

Apprentices Previous Month =

CALCULATE(

    SUM(HR_data[No. of Apprentice]),

    FILTER(

        HR_data,

        HR_data[Period] = EDATE(MAX(HR_data[Period]), -1)

    )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[danextian]

Hi @Fusilier2 

That is because -1 is enclosed in MAX as well. Try this:

CALCULATE (
    SUM ( HR_data[No. of Apprentice] ),
    FILTER ( HR_data, HR_data[Period] = MAX ( HR_data[Period] ) - 1 )
)

This measure works if there isn't a filter coming from Period. For example, if you select Period 1, the measure will return blank as DAX sees Period 1 and not Period - 1 . You can change your measure to the following

CALCULATE (
    SUM ( HR_data[No. of Apprentice] ),
    FILTER ( ALL ( HR_data, HR_data[Period] ) = MAX ( HR_data[Period] ) - 1 )
)

 This aside, I would start using a dedicate date dimension table to simply time intelligence calculations.

[Fusilier2 · Accepted solution]

Thank you for your help.

I shall investigate date dimension tables when I get some more time.

In the meantime, your suggestion didn't work. It returned nil.

However I got this to work:

Apprentices Prior month  =

CALCULATE(

    SUM(HR_data[No. of Apprentice]),

    FILTER(

        ALL(HR_data[Period]),

        HR_data[Period]=EOMONTH(MAX(HR_data[Period]),-1)

        )

    )

  Which seems OK for me now.      

[v-sgandrathi]

Hi @Fusilier2,

I'm glad you found a solution and resloved  the query. Thank you very much for sharing here.

Kindly mark your reply as the accepted solution so that others in the community can find it quickly.

Thankyou for connecting with Microsoft Community Forum.

[v-sgandrathi]

Hi @Fusilier2 ,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community.

Thank you again for your cooperation!

[Ritaf1983]

Hi @Fusilier2 
You should use the dim date table in your model , it will help use time intellegence functions.

Please refer to the linkded video:
https://www.youtube.com/watch?v=-gEhRzdhKro

For more specific suggestions Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.