Changing the context filter based inside a calculation

[Mr_Robot0092]

Hello,

I have a dimension that have the status of client(Active,inactive,...) , and i have a fact table that contains all my client with the status id and date key.

The status of client can change every day.

I have a report with a slicer on date and also some other filter in other dimension.

I have a table that show me the count of my client of each status .

NB Client	Status
17132		 ACTIVE
1530	     INACTIVE
152            N/A

What i want is calculate the NB Client of each status and Adding and New status called Last Day Inactive  that show me the Nb Client that are inactive in my last Date of my filter.

Example : 

In my slicer i selected two Date from 1/04/2022 to 10/04/2022 .

The idea is calculate the NB Client in my Last Date in my case 10/04/2022 and showing the value , i want something like as bellow :

NB Client	Status
17132		 ACTIVE
1530	     INACTIVE
152            N/A
15	    Last Day INACTIVE

What i did , i create Calculated table "DimStatusTR" based on my Dimension table like this : 

UNION (
    ROW (
        "Status_Key", "99",
        "Status_Label", "Last Day Inactive"
    ),
    SUMMARIZECOLUMNS (
        'DimStatus'[Status_Key],
        'DimStatus'[Status_Label]
    )
)

I did a relationship between this new dimension and my Fact table , after that , i created a measure like as bellow : 

Var _InactiveLAstDay  = CALCULATE(
    [NbClient],
    FILTER(
        Fact_Client,
        RELATED('DimStatusTR'[Status_Label]) = "Inactive" &&
        Fact_Client[Date_Key] = MAX(Fact_Client[Date_Key])
    )
)


Var _OtherStatus = [NbClient]

Var _current = SELECTEDVALUE('DimStatusTR'[Status_Label])

Var result = if(_current ="Last Day Inactive",_InactiveLAstDay  +0,_OtherStatus)

Return result

The result that i get is like as bellow : 

NB Client	Status
17132		 ACTIVE
1530	     INACTIVE
152            N/A
0             Last Day INACTIVE

What i want is something like that 

NB Client	Status
17132		 ACTIVE
1530	     INACTIVE
152            N/A
15	    Last Day INACTIVE

Any idea how can i do that ?

Thanks for help ! 

[amitchandak]

@Mr_Robot0092 , First of all you need a date wise status for that. Now you can have column on meausre for what you need

Like the last status column 

=

var _max = maxx(filter(Table, [NB Client] = earlier([NB Client])  && [Date]  < Earlier([Date]) ) , [Date])

return

maxx(filter(Table, [NB Client] = earlier([NB Client])  && [Date]  =_max ) , [Status])

for measure refer

Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[Mr_Robot0092]

Hello @amitchandak 

Thanks for replying .I tried to do your code but doesn't work i this [NB Client] = earlier([NB Client]) i have this error the parameter is not not the correct type .

The [NB Client] is measure with Distinctcount(client_id)
Any idea ?