Dax support

[vjnvinod]

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"

and  if my coloumn event date is current month show me that as "MMM"-YYYY"  for example Jun-2019

and if my coloumn event date is blank show me that as " Upcoming"

[vivran22]

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

@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 · Accepted solution]

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

[hthota]

if('Master Session Tracker'[Event Date]=BLANK(),Upcoming,if('Master Session Tracker'[Event Date]=curentmonth('Master Session Tracker'[Event Date]),format('Master Session Tracker'[Event Date],"MMM-YYYY",Year('Master Session Tracker'[Event Date]))

I hope it would help you.

Thanks & Regards,

Hemanth Thota.

[hthota]

if('Master Session Tracker'[Event Date]=BLANK(),"Upcoming",if('Master Session Tracker'[Event Date]=curentmonth('Master Session Tracker'[Event Date]),format('Master Session Tracker'[Event Date],"MMM-YYYY",Year('Master Session Tracker'[Event Date]))

I hope it would help you.

Thanks & Regards,

Hemanth Thota.

[vjnvinod]

@hthota 

Hi,

i am getting some error like below

[hthota]

Please provide any date field inside the previousmonth function. For example event date.

[Anonymous]

Any particular reason you want these as calculated columns vs. using measures?  

[vjnvinod]

@Anonymous 

i think measure can also help.

can you help me to create a measure for this issue?