topic Re: Calculating SUM for the previous last month in DAX Commands and Tips

Calculating SUM for the previous last month

NChamroo — Thu, 06 Jun 2024 06:35:36 GMT

Hello Experts,

I have a data set, the actuals and forecast value are in the same column and I am trying to get the Sum of Actuals by month and YTD for the previous months. I need to exclude the current month and the remaining year months. My below formula is returning the total instead of the monthly when I am adding the measure in a matrix.

Actual BHR (UC) =

CALCULATE(

SUM('Actual_Forecast[Block Hour]'[Block Hours]),

FILTER(

ALL('Calendar Date'[Date].[Date]),

'Calendar Date'[Date].[Date] < EOMONTH(TODAY(), -1) &&

'Calendar Date'[Date].[Date] >= DATE(YEAR(TODAY()), 1, 1)

)

Re: Calculating SUM for the previous last month

bhanu_gautam — Thu, 06 Jun 2024 12:29:07 GMT

NChamroo It looks like syntax error can you try with below mentioned DAX once

Actual BHR (UC) = CALCULATE(
SUM('Actual_Forecast'[Block Hours]),
FILTER(
ALL('Calendar Date'[Date].[Date]),
'Calendar Date'[Date].[Date] < EOMONTH(TODAY(), -1) &&
'Calendar Date'[Date].[Date] >= DATE(YEAR(TODAY()), 1, 1)
)
)

Re: Calculating SUM for the previous last month

NChamroo — Tue, 11 Jun 2024 08:52:36 GMT

Thanks Gautam, thats perfect

Re: Calculating SUM for the previous last month

bhanu_gautam — Tue, 11 Jun 2024 13:01:39 GMT

NChamroo Please accept this as solution if it helped you