DAX YTD Function with Slicer

[Anonymous]

Hi,

I am realtively new user of power BI. I have two tables -1.Forecast 2. Calender Lookup in my model and i had created 'many to one' cardinality between them. Now wanted to create DAX function to get the YTD forecast numbers based on my calender month slicer embeded at the top of my report. The following i've used to calculate YTD forecast function:

1. Max = EOMONTH(MAXX(ALLSELECTED(Lookup),Lookup[Date]),0)

2. YTD Forecast = CALCULATE(sum(Forecast[Forecast]),DATESYTD(Lookup[Date]),FILTER(Lookup,Lookup[Date]<=[Max]))

however i am getting same result in both sum as well as YTD function once i select a middle month of a year , lets say 'May-21' as per screenshot below, which should not be!

any idea why is that so?@amitchandak

P.S: i have formated both date column in both table as 'date' using the query editor as well.

[parry2k · Accepted solution]

@Anonymous you don't need this code for YTD

,FILTER(Lookup,Lookup[Date]<=[Max])

Also did you marked the calendar lookup table as a date table?

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Anonymous great. Although it doesn't matter you marked your reply as an answer. Always marked the correct reply as an answer so that others can find the solution. FYI

[Anonymous]

Yes i did, and it's working. Thank you so much.

[parry2k · Accepted solution]

@Anonymous you don't need this code for YTD

,FILTER(Lookup,Lookup[Date]<=[Max])

Also did you marked the calendar lookup table as a date table?

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡