YTD calculation issue

[PBIDEV_10]

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

[FarhanAhmed · Accepted solution]

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.

[PBIDEV_10]

@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?

[FarhanAhmed]

See if this helps 

Optimizing DAX expressions involving multiple measures - SQLBI

[amitchandak]

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

[PBIDEV_10]

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