topic Re: DAX formula to get Previous month from Max Month in DAX Commands and Tips

DAX formula to get Previous month from Max Month

loos — Mon, 16 Jan 2023 12:03:07 GMT

I have this formula that works well, it is based off of the user selecting multiple months in a filter, but using only the latest to calculate it:

Current Month FTE = CALCULATE(SUM(HRdata[FTE]), FILTER(HRdata, HRdata[YearMonth]= (MAX(HRdata[YearMonth]))))

However, I need a second formula that calculates the previous month of the MAX month.

This does not work:

Previous Month FTE = CALCULATE(SUM(HRdata[FTE]), FILTER(HRdata, HRdata[YearMonth]= DATEADD(MAX(HRdata[YearMonth],-1, Month))))

Can someone help me fix it?

Thank you!

Re: DAX formula to get Previous month from Max Month

tamerj1 — Mon, 16 Jan 2023 12:04:09 GMT

Hi loos
Allways better to have a date table. However, if you wish not not to use one please try

Previous Month FTE = VAR CurrentYearMonth = MAX ( HRdata[YearMonth] ) VAR PreviousYearMonth = TOPN ( 1, FILTER ( ALL ( HRdata[YearMonth] ), HRdata[YearMonth] < CurrentYearMonth ), HRdata[YearMonth] ) RETURN CALCULATE ( SUM ( HRdata[FTE] ), FILTER ( ALL ( HRdata ), HRdata[YearMonth] = PreviousYearMonth ) )

Re: DAX formula to get Previous month from Max Month

loos — Mon, 16 Jan 2023 12:37:03 GMT

I should have mentioned one more thing, there are other filters (slicers) that also exist. So if I have no filters selected other than the date slicer, your formula works. But if I use the other slicers to select the type of company, etc, the Previous Month number stays the same and does not drill down alongside the Current Month. Is there anyway to allow the other filters to affect this number?

Re: DAX formula to get Previous month from Max Month

tamerj1 — Mon, 16 Jan 2023 12:45:19 GMT

loos
Please provide a screenshot for better understanding

Re: DAX formula to get Previous month from Max Month

tamerj1 — Mon, 16 Jan 2023 12:55:37 GMT

loos
You may also try

Previous Month FTE = VAR CurrentYearMonth = MAX ( HRdata[YearMonth] ) VAR PreviousYearMonth = TOPN ( 1, FILTER ( ALL ( HRdata[YearMonth] ), HRdata[YearMonth] < CurrentYearMonth ), HRdata[YearMonth] ) RETURN CALCULATE ( SUM ( HRdata[FTE] ), HRdata[YearMonth] = PreviousYearMonth, ALLEXCEPT ( HRdata, HRdata[FilterSafeColumn1], HRdata[FilterSafeColumn2], ....etc ) )

Re: DAX formula to get Previous month from Max Month

loos — Mon, 16 Jan 2023 13:10:11 GMT

Thank you for your help, I was able to finally figure out the solution:

Previous Year FTE = CALCULATE(sum(HRdata[FTE]),FILTER(ALL(HRdata[YearMonth]),HRdata[YearMonth] = DATE(YEAR(MAX(HRdata[YearMonth])),MONTH(MAX(HRdata[YearMonth])-1),DAY(MAX(HRdata[YearMonth])))))

Re: DAX formula to get Previous month from Max Month

tamerj1 — Mon, 16 Jan 2023 13:30:08 GMT

loos
I thought your YearMonth column is an integer data type. Just now I realized it is a Date data type. This is the importance of posting some sample data along with the expected results.