Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
7 years ago
Solved

Dax support

Hi team,   I have a  table (Master Session Tracker) where i have coloumn Event Date,  i want to create a new coloumn wherein if my coloumn event date is all previous months show me that as "YTD" ...
Show and Tell
  • vivran22's avatar
    vivran22
    7 years ago

    vjnvinod 

     

    Date(Year([Event Date]),Month([Event Date]),1) 

    This is calculating the first day of the month of the EventDate in date format. For example, if the EventDate is 12-Jun-2019, then this formula is converting it to 1-Jun-2019

     

    Date(Year(Today()),Month(Today()),1)

    This formula is checking today's date and then calculating the first day of the month (same as logic above)

     

    So essentially I am first converting the EventDate and Today's date to first of the month and then applied the condition you had mentioned.

     

    FORMAT([Event Date],"MMM-YYYY"

    This formula is converting the date into the required format.

     

    Hope this answers your query.

     

    Thanks,

    Vivek

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

vivran22's avatar
vivran22
Community Champion
7 years ago

vjnvinod 

 

Hi,

 

Please try this:

(This is considering that you are comparing the EventDate with current date, as it is not clear from your orignal query)

 

Column = If( [EventDate] = Blank()
,"Upcoming"
,If
(Date(Year([EventDate]),Month([EventDate]),1) < Date(Year(Today()),Month(Today()),1)
    , "YTD",If(Date(Year([EventDate]),Month([EventDate]),1) = Date(Year(Today()),Month(Today()),1),FORMAT(Table1[EventDate],"MMM-YYYY"
))))
 
Thanks,
Vivek
 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vjnvinod's avatar
vjnvinod
Impactful Individual
7 years ago

vivran22 

 

This is perfect & great, can you also explain me the working of below 2 conditions you have used?

 

If(Date(Year([Event Date]),Month([Event Date]),1) < Date(Year(Today()),Month(Today()),1), "YTD"
If(Date(Year([Event Date]),Month([Event Date]),1) = Date(Year(Today()),Month(Today()),1),FORMAT([Event Date],"MMM-YYYY"
  • vivran22's avatar
    vivran22
    Community Champion
    7 years ago

    vjnvinod 

     

    Date(Year([Event Date]),Month([Event Date]),1) 

    This is calculating the first day of the month of the EventDate in date format. For example, if the EventDate is 12-Jun-2019, then this formula is converting it to 1-Jun-2019

     

    Date(Year(Today()),Month(Today()),1)

    This formula is checking today's date and then calculating the first day of the month (same as logic above)

     

    So essentially I am first converting the EventDate and Today's date to first of the month and then applied the condition you had mentioned.

     

    FORMAT([Event Date],"MMM-YYYY"

    This formula is converting the date into the required format.

     

    Hope this answers your query.

     

    Thanks,

    Vivek

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.