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

6 Replies

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

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

      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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        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