Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX YTD Function with Slicer

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.

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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's avatar
    parry2k
    5 years ago

    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

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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.⚡

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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