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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
NChamroo
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)
    )
)
NChamroo_0-1717655668789.png

 

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
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!




LinkedIn






View solution in original post

3 REPLIES 3
bhanu_gautam
Super User
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!




LinkedIn






Thanks Gautam, thats perfect

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




LinkedIn






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.