cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Fabric Community Update - April 2024

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

Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors