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

Helper II

## YTD calculation issue

Hello experts!

I have a calculation for new members where it says till 31.05.2021(CutOverDate) read value from source 2 else from source 1 (Total new members = IF(MAX(Calendar[Date])>= [CutOverDate],[Members source 1], [Members source 2])). This measure shows correct values. But when I use it in YTD calculation (CALCULATE([Total new members],DATESYTD(Calendar[Date])), for the value of june 2021, it shows summed value for jan21 to june21 from source 1 and ignores the logic for Total new members. Please refer to the pic below. For June 2021 instead of showing 19955 it should show 13834(10869+2965). How to get YTD calculation right?

Regards

PP

1 ACCEPTED SOLUTION
Community Champion

I think first You should create a New intermediate measure to calculate the correct calculation for totals.

``````Total  Members =
SUMX(VALUES(Dates[Month & Year]),[Total new members])``````

Now, this measure should be used in YTD measure to get correct YTD numbers.

Proud to be a Super User!

5 REPLIES 5
Community Champion

I think first You should create a New intermediate measure to calculate the correct calculation for totals.

``````Total  Members =
SUMX(VALUES(Dates[Month & Year]),[Total new members])``````

Now, this measure should be used in YTD measure to get correct YTD numbers.

Proud to be a Super User!

Helper II

@FarhanAhmed : Thank you for your tip. It worked. But instead of month, I used date since user needs to drill down till day level. (Total members test = SUMX(VALUES(Calendar[Date]),[Total new members])). When I use it in the report then it takes lot of time to process the data than before. Can I do anything to load the data faster?

Community Champion

See if this helps

Optimizing DAX expressions involving multiple measures - SQLBI

Proud to be a Super User!

Super User

@PBIDEV_10 make sure calendar is marked as date table and you are using year, month, and month name from calendar table

Helper II

@amitchandak Yes. Calendar is marked as a date table and I am using year, month from the same table.

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