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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MiraNi
Advocate III
Advocate III

Cumulative YTD including only past months from current year and corresponding months from prev years

I have a Order revenuew YTD measure, which is showing monthly values only for past months in current year and corresponding months in all previous years. DAX for this is:

  CALCULATE(
        [Actual order revenue, sum],
        'Project - Calendar Period'[IsYTD] = TRUE()
        )

 

Now I need to have similar measure but as cumulative version and cumulative prev year version.

I have already a cumulative YTD measure but it is showing values for all months, not only for months as defined in first measure. DAX for this measure is:

CALCULATE([Actual order revenue, sum], DATESYTD('Project - Calendar Period'[Date]))

How do I get a wanted result, how should I build the measure and the prev year version of it? I have tried so many different ways and there is always a different problem. 

 

 

1 ACCEPTED SOLUTION
MiraNi
Advocate III
Advocate III

I got a solution somewhere else. There must be different ways to do this but at least DAX below works.
But how about a previous year version of this measure?
 
var currentDate = CALCULATE( max('Project - Calendar Period'[Date]), 'Project - Calendar Period'[IsYTD] = TRUE() )
var yearnum = CALCULATE( max('Project - Calendar Period'[YearNo]), 'Project - Calendar Period'[IsYTD] = TRUE() )
return
CALCULATE([Actual order revenue, sum],
    ALL('Project - Calendar Period'),
    'Project - Calendar Period'[YearNo] = yearnum,
    'Common - Calendar'[Date] <= currentDate
)

View solution in original post

2 REPLIES 2
MiraNi
Advocate III
Advocate III

I got a solution somewhere else. There must be different ways to do this but at least DAX below works.
But how about a previous year version of this measure?
 
var currentDate = CALCULATE( max('Project - Calendar Period'[Date]), 'Project - Calendar Period'[IsYTD] = TRUE() )
var yearnum = CALCULATE( max('Project - Calendar Period'[YearNo]), 'Project - Calendar Period'[IsYTD] = TRUE() )
return
CALCULATE([Actual order revenue, sum],
    ALL('Project - Calendar Period'),
    'Project - Calendar Period'[YearNo] = yearnum,
    'Common - Calendar'[Date] <= currentDate
)
lbendlin
Super User
Super User

Add a filter

 Month('Project - Calendar Period'[Date])<Month(TODAY())

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors