Forum Discussion

msommerf's avatar
msommerf
Icon for Helper III rankHelper III
6 years ago
Solved

Date Slicer - Custom Phases

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 dat...
Need Help
Show and Tell
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
6 years ago

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's avatar
msommerf
Icon for Helper III rankHelper III
6 years ago

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.