cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors