Custom Prior Period (NO DATE or INDEXES)

[bajimmy1983]

Hi Community, how are you? 

I'm really trying to think out of the box regarding to this very custom scneario, but I have to confess I failed in all attempts. Could you please try to guide me, help me? 

What I need is quite simple, but due to lacking of date column or indexes in this case, I simply don't know how to accomplish it. 

I need to create, simulate a kind of "EARLIER" function or "Prior Period" as ilustrated below. Is there anyway to do so? 

Thank you so much for your help.

Jimmy!

[amitchandak]

@bajimmy1983 , if you can create an order(Index) for this that you can use that index to get that

new Column =  maxx(filter(Table,[Pilot Index] =earlier([Pilot Index] )-1),[V1 Pilot])

New Measure = CALCULATE(sum('Table'[V1 Pilot]), FILTER(ALL('Table'),'Table'[Pilot Index]=max('Table'[Pilot Index])-1))

[bajimmy1983]

Hi again @amitchandak! Sorry for delaying my reply.

I was trying to digest your help and trying to aplly it yesterday. 

You helped me a lot because I could adapt my Measures, but they are not the same as your 100%, but we are almost there. 

Also, sorry for not sharing the model. Its big and there are many tables. 

Could you please at your pace take another look to the new pictures I'm attaching now, in this reply... Maybe you will find fast what I'm doing wrong. I tried to be very  illustrative per images below.

DISCONNECTED TABLEMATRIX VISUALCURRENT VERSION MEASUREPRIOR VERSION MEASURE

MEASURES: 

Current Version = 
VAR _Currency =
    VALUES ( CurrencySelection[Currency] )
VAR _IgnoreKPI =
    ALL ( BD_Pilot[TitleName] )
VAR _KPI =
    SELECTEDVALUE ( PilotTitleBD_Disc_Slicers[TitleName] )
VAR _IgnorePilotBase =
    ALL ( BD_Pilot[Pilot Base] )
VAR _Basis =
    VALUES ( PilotBase_Disc[BaseIndex] )
VAR BasicCalc =
    CALCULATE (
        SUMX (
            BD_Pilot,
            DIVIDE (
                BD_Pilot[BRL],
                LOOKUPVALUE (
                    ExRate[ExchangeRate],
                    ExRate[Date], BD_Pilot[Date],
                    ExRate[ForeignCurrency], _Currency,
                    ExRate[From], "GENERAL"
                )
            )
        ),
        _IgnoreKPI,
        _IgnorePilotBase,
        BD_Pilot[TitleName] = _KPI,
        BD_Pilot[BaseIndex] IN _Basis
    )
RETURN
    SWITCH (
        _Currency,
        "BRL", BasicCalc,
        "EUR", BasicCalc,
        "BRL (thousands)", DIVIDE ( BasicCalc, 1000 ),
        "EUR (thousands)", DIVIDE ( BasicCalc, 1000 )
    )

Prior Version = 
VAR _Currency =
    VALUES ( CurrencySelection[Currency] )
VAR _IgnoreKPI =
    ALL ( BD_Pilot[TitleName] )
VAR _KPI =
    SELECTEDVALUE ( PilotTitleBD_Disc_Slicers[TitleName] )
VAR _IgnorePilotBase =
    ALL ( BD_Pilot[Pilot Base] )
VAR _Basis =
    SELECTEDVALUE( PilotBase_Disc[Earlier] )
VAR BasicCalc =
    CALCULATE (
        SUMX (
            BD_Pilot,
            DIVIDE (
                BD_Pilot[BRL],
                LOOKUPVALUE (
                    ExRate[ExchangeRate],
                    ExRate[Date], BD_Pilot[Date],
                    ExRate[ForeignCurrency], _Currency,
                    ExRate[From], "GENERAL"
                )
            )
        ),
        _IgnoreKPI,
        _IgnorePilotBase,
        BD_Pilot[TitleName] = _KPI,
        FILTER( BD_Pilot, BD_Pilot[BaseIndex] = MAX( PilotBase_Disc[Earlier]  ) )

    )
VAR _Calc = SWITCH (
        _Currency,
        "BRL", BasicCalc,
        "EUR", BasicCalc,
        "BRL (thousands)", DIVIDE ( BasicCalc, 1000 ),
        "EUR (thousands)", DIVIDE ( BasicCalc, 1000 )
    )
RETURN
    IF( [v1 Pilot All Basis by Pilot Base Date] <> BLANK(), _Calc, BLANK()  
)

Thank you so much again for all your effort. If it's too difficult to help, no problem, thanks anyway! Be safe!

[v-xicai]

Hi @bajimmy1983 ,

You may create column and measure like DAX below.

Columns:

Year= YEAR('PilotBase_Disc'[PilotBase_Date])

Index=CALCULATE(COUNT('PilotBase_Disc'[PilotBase_Date]),FILTER(ALLSELECTED('PilotBase_Disc'),'PilotBase_Disc'[Year]=EARLIER('PilotBase_Disc'[Year])))



Measure:

Prior Version= CALCULATE(([Current Version]),FILTER(ALLSELECTED('PilotBase_Disc'),'PilotBase_Disc'[Index]=MAX('PilotBase_Disc'[Index])-1))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bajimmy1983]

Hi there @v-xicai, good morning! 

Thank you so much for your big contribution the same as @amitchandak. 

I'll try to adapt like you suggested and let you know. 

Have a nice day 🙂

[bajimmy1983]

 Hi @amitchandak. Really thank you so much for your fast reply! 

Please let me try to implement what you are suggesting. 

🙂