DAX Formula IF Statement Dates

[msommerf]

Hi,

Please could somebody help with the following?

I use this DAX statement below to Calculate the current period in a column for the current month in my Dates table.

Phase Selection = If(

month(Dates[Date])=Month(TODAY()) && YEAR(Dates[Date])=YEAR(TODAY()),

"Current Phase", Dates[Phase Year])

This populates the column with "Current Phase" for dates in the current month, all other records display the Dates[Phase Year]) data. I need to amend this code so that rather than displaying Current Phase for just this month, I need it to display "Current Phase" for all dates in the following group:

Phase 1 = Months 1-7 Inclusive

Phase 2 = Months 8 - 12 Inclusive

If todays date is 3/3/2018 I need the column to display "Current Phase" for all dates between 01/01/2018 and 31/7/2018.

If todays date is 01/08/2018 I need the column to display "Current Phase" forall dates between 01/08/2018 - 31/12/2018.

Any assistance appreciated.

[Anonymous · Accepted solution]

@msommerf  - You could add a "Phase" column to your date table. Then in you could do this for your Calculated Column:

Phase Selection=
var phase_today = CALCULATE(max('Dates'[Phase]) ,'Dates'[Date] = TODAY())
return IF([Phase] = phase_today, "Current Phase", "Not Current Phase")

Cheers!

Nathan

[Anonymous · Accepted solution]

@msommerf  - You could add a "Phase" column to your date table. Then in you could do this for your Calculated Column:

Phase Selection=
var phase_today = CALCULATE(max('Dates'[Phase]) ,'Dates'[Date] = TODAY())
return IF([Phase] = phase_today, "Current Phase", "Not Current Phase")

Cheers!

Nathan

[msommerf]

@Anonymous 

Nathan,

Thanks for such a quick response.

Using your DAX, only today is set as the current phase?

I have a Phase column in my dates table (Phase) which has phases stored as:

2017 Phase 1

2017 Phase 2

2018 Phase 1

2018 Phase 2

2019 Phase 1

2019 Phase 2

If current month is April  I need to set the current phase against months of Jan - Jul

If current month is August I need to set the current phase against months of Aug - Dec

regards

Mark.

[Anonymous]

@msommerf - It works here - please share a screenshot of your results.

[msommerf · Accepted solution]

Sorry if it's something stupid I am doing at this end.

Just learning the rope with DAX.

[msommerf]

Hi Nathan,

Spotted my mistake.

I did not change the second column name in the code.

This works perfectly 🙂

Many thanks for your help.

Regards

Mark.