cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Calculating SUM for the previous last month

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

1 ACCEPTED SOLUTION
Super User

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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

3 REPLIES 3
Super User

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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Regular Visitor

Thanks Gautam, thats perfect

Super User

@NChamroo Please accept this as solution if it helped you

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.