Skip to main content
cancel
Showing results for 
Search instead 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

Reply
BITomS
Resolver IV
Resolver IV

Use Max Date Month From Fact Table in Prior Years in Matrix

Hi all,

This problem is a blocker to a wider calculation (YTD measure), but am just showing this specific part in the hope to make it easier to resolve.

I have a number of years in my matrix table and want to identify the Max Month in the current year (i.e. May 2024 which is showing correctly as 5 below), but also apply this same month to previous years to make it dynamic. However, for the previous years, the YTD month returned is for the full year i.e. 12.

 

Please may someone help me understand how I can ensure 2022 and 2023 years also show 5 instead of 12?

FYI, the max date is taken from a fact table (DailyConsumptions), not a Date dimension table, although a date dimension table does exist in the model.

 

BITomS_0-1718289017572.png

 

1 ACCEPTED SOLUTION
BITomS
Resolver IV
Resolver IV

Fixed it myself:

VAR _Month = MONTH(calculate(max(DailyConsumptions[Date]),removefilters('Date')))

View solution in original post

1 REPLY 1
BITomS
Resolver IV
Resolver IV

Fixed it myself:

VAR _Month = MONTH(calculate(max(DailyConsumptions[Date]),removefilters('Date')))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.