Select Current and Prior Period when the Date is text format

[spartanboy]

Hello - 

The slicer is set to "Single Select". The "TIME_FRAME" column can contains actual dates or text (like, 2022Q1, 2022Q2, 2022H1, 2022H2). If the user selects 2022Q2, the measure should result in 2022Q1. What needs to be changed in my attempt below?

Evetually, I would like to calculate change % from the selected time with prior time.

Calculate_PRIOR_PERIOD = 
VAR _selectedDates = VALUES(F_COST[TIME_FRAME])
var _currentperiod = maxx(ALLSELECTED(F_COST[TIME_FRAME]),F_COST[TIME_FRAME])
var _priorperiod = ??? - "here the logic should be to provide result as 2022Q1"
RETURN _priorperiod

 TIA

[v-luwang-msft]

Hi @spartanboy ，

Test the below:

test =
VAR maxvalue =
    CALCULATE (
        MAX ( D_DATE_TIME[Rank] ),
        FILTER (
            ALL ( D_DATE_TIME ),
            'D_DATE_TIME'[Rank] < MAX ( 'D_DATE_TIME'[Rank] )
        )
    )
RETURN
    CALCULATE (
        MAX ( D_DATE_TIME[TIME_FRAME] ),
        FILTER ( ALL ( D_DATE_TIME ), D_DATE_TIME[Rank] = maxvalue )
    )

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

@spartanboy 
Assume if you have dimension table as QTR and data something like below for quarters in wholenumber which has relationship with your fact table where the measures are coming from.
I have created the rank for each quarter using dax.

Rank = RANKX(Qtr,Qtr[Qtr No],,ASC,Dense)

Then, I have created a standalone calculated table as QTR_Slicer from the above table using below dax.

This table is used for only to select the quarters using slicer.

then use the below measure to calcualte the prior quarter values.

CALCULATE([Your Measure]),
Qtr[Rank]  =  SELECTEDVALUE(Qtr_Slicer[Rank])-1))

Accept the solution if that works for you.

Thanks,

Mohan V.

[spartanboy]

@Anonymous Thanks for assistance!

First I created a table that contains unique quarters and rank, and use this 'TIME_FRAME' as a slicer.

Calculate_CURRENT_PERIOD = 
VAR _selectedDates = VALUES(D_DATE_TIME[TIME_FRAME])
var _currentperiod = maxx(ALLSELECTED(D_DATE_TIME[TIME_FRAME]),D_DATE_TIME[TIME_FRAME])
RETURN _currentperiod

With your final measure i am getting a rank of the prior period. How to get the PRIOR_PERIOD i.e. 2022Q1 when slicer selection is 2022Q2?