Date Slicer - Custom Phases

[msommerf]

Hi,

I have a date table which defines the phases of a project every year as follows:

Phase 1 = 1st January - 31st July

Phase 2 = 1st August - 31st December

I have created a column in my date table to work out and display the Current Phase so this can be used in a slicer using:

Phase Selection =

var phase_today = CALCULATE(max('Dates'[Phase Year]) ,'Dates'[Date] = TODAY())

return IF([Phase Year] = phase_today, "Current Phase", Dates[Phase Year])

Can anyone help me dynamically display the Previous Phase?

So if the current phase = 2020 Phase 1, the previous phase would be 2019 Phase 2

if the current phase = 2019 Phase 2, the previous phase would be 2019 Phase 1 etc...

Any assistance appreciated.

[v-lili6-msft]

hi  @msommerf 

Just create a column as below:

previous phase = IF(RIGHT(Dates[Phase Year],1)="2",LEFT(Dates[Phase Year],4)&" Phase 1",VALUE(LEFT(Dates[Phase Year],4))-1&" Phase 2")

Regards,

Lin

[msommerf]

hi @v-lili6-msft 

Many thanks for your response.

Your solution works in that it is displaying the previous Phase Year.

I was looking for the column to display the words "Previous Phase" in this column and not display the phase year value.

[v-lili6-msft · Accepted solution]

HI  @msommerf 

Ok, try this way:

previous phase = 
var _current= CALCULATE(MAX(Dates[Phase Year]),FILTER(Dates,Dates[Phase Selection]="Current Phase")) return
IF(IF(RIGHT(_current,1)="2",LEFT(_current,4)&" Phase 1",VALUE(LEFT(_current,4))-1&" Phase 2")=Dates[Phase Year],"Previous Phase",Dates[Phase Year])

Regards,

Lin

[msommerf]

@v-lili6-msft 

This is perfect!

Many thanks for a quick response 🙂

[Pragati11]

Hi @msommerf ,

Create a caluclated column as follows:

Previous = CONCATENATE(LEFT(SUBSTITUTE(phase[Current]," ","-"),SEARCH("-",SUBSTITUTE(phase[Current]," ","-"))-1), CONCATENATE(" ", (RIGHT(SUBSTITUTE(phase[Current]," ","-"),LEN(SUBSTITUTE(phase[Current]," ","-"))-SEARCH("-",SUBSTITUTE(phase[Current]," ","-"))) - 1)))

Replace  phase[Current] in above DAX with your table-name and phase-year column.

Thanks,

Pragati

[msommerf]

Thank you for your quick response.

I have created a calculated column using your DAX formula as follows:

Previous Phase = CONCATENATE(LEFT(SUBSTITUTE(Dates[Phase Year]," ","-"),SEARCH("-",SUBSTITUTE(Dates[Phase Year]," ","-"))-1),

CONCATENATE(" ", (RIGHT(SUBSTITUTE(Dates[Phase Year]," ","-"),LEN(SUBSTITUTE(Dates[Phase Year]," ","-"))

-SEARCH("-",SUBSTITUTE(Dates[Phase Year]," ","-"))) - 1)))

But I get the following error:

"Cannot convert value 'Phase-1' of type text to number"

 Regards

Mark.