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

## 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
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
)
2 REPLIES 2
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
)
Super User

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

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.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors