Forum Discussion
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
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.
5 Replies
PBIDEV_10 make sure calendar is marked as date table and you are using year, month, and month name from calendar table
amitchandak Yes. Calendar is marked as a date table and I am using year, month from the same table.
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.
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?
