History table, compare state and find gain/loss

[Madxfr]

Hello,

I’m stuck with my Power Bi

I have a history table with an id, date of historisation, state. Datas are logged every month.

My needs :

I would like to compare the actual data to another month selected by the user by a filter

Needed  Indicators:

Nb of Pv with Etat = Exploitable in the actual month

Gain indicator for Nb of Pv with Etat = Exploitable vs selected month

Nb of Gain* (actual month) vs selected month

Nb of Lost* (actual month) vs selected month

*Gain formula :

If (Actual Etat=Exploitable  and (Pev Etat = Inexploitable or Pev Etat=Non contractualisé)) Then flag = 1

*Lost formula :

If ( (Actual Etat= Inexploitable or Actual Etat=Non contractualisé) and Pev Etat = Exploitable) Then flag = 1

2 flags Gain, Lost to a tab

I already get results but i don’t know if i ‘m in the good way.

Some of my mesures:

Date Max = CALCULATE(

    MAX( DateHisto[Date_Histo] ),

    ALL(DateHisto)

)

PV exploitable = CALCULATE(DISTINCTCOUNT(Patrimoine[Fk_Pv]),FILTER(ALL (DateHisto[Date_Histo]),DateHisto[Date_Histo]=[Date Max]),FILTER(ALL (Etat),Etat[StatutId]=1))

PV exploitable Prev = CALCULATE(DISTINCTCOUNT(Patrimoine[Fk_Pv]),FILTER(ALL (Etat),Etat[StatutId]=1))

Pv exploitable Diff = [PV exploitable]-[PV exploitable PreV]

Pv Exploitable Arrow = SWITCH (

TRUE (),

    [Pv exploitable Diff]>0,CONCATENATE("▲ " , [PV exploitable]),

    [Pv exploitable Diff]<0,CONCATENATE("▼ " , [PV exploitable]),

    CONCATENATE("= " , [PV exploitable])

    )

Actual Etat = CALCULATE(SELECTEDVALUE(Patrimoine[Statut]),FILTER(ALL (DateHisto[Date_Histo]),DateHisto[Date_Histo]=[Date Max]))

EtatId a date = CALCULATE(SELECTEDVALUE(Patrimoine[StatutId]),FILTER(ALL (DateHisto[Date_Histo]),DateHisto[Date_Histo]=[Date Max]))

Prev Etat = CALCULATE(SELECTEDVALUE(Patrimoine[Statut]))

EtatId Prec = CALCULATE(SELECTEDVALUE(Patrimoine[StatutId]))

Gain = IF(isblank(_Mesures[EtatId a date]),BLANK(),if(_Mesures[EtatId a date]=1 && _Mesures[EtatId Prec]<=0,1,0))

Lost = IF(isblank(_Mesures[EtatId a date]),BLANK(),if(_Mesures[EtatId a date]<=0 && _Mesures[EtatId Prec]=1,1,0))

For the moment i'm stuck to count the number of Gain* or Lost*.

Is it the good way to create my mesures ?

Thanks for your help.

Data model

[lbendlin]

Most of this looks ok.  Can you please explain what you are trying to achieve with this?

Prev Etat = CALCULATE(SELECTEDVALUE(Patrimoine[Statut]))

Usually SELECTEDVALUE is used outside of CALCULATE

[Madxfr]

Hello,

You are right, i change for

Prev Etat = SELECTEDVALUE(Patrimoine[Statut])
EtatId Prec = SELECTEDVALUE(Patrimoine[StatutId])

Results are the same.

My problem is to get the sum of In (Gain) or sum of Lost.

In (Gain)  is a mesure depending of actual EtatId and previous Etatid of the row.

The DAX is :

In = IF(isblank(_Mesures[EtatId a date]),BLANK(),if(_Mesures[EtatId a date]=1 && _Mesures[EtatId Prec]<=0,1,0))

As you can see on the table it's ok, but the total is wrong and if i add a card with the mesure i always get 1

I don't know what's wrong.

[lbendlin]

Always getting 1 as a result points to a row context issue. You may want to read about iterator functions like SUMX.

[Madxfr]

I tried:

Measure = SUMX(_Mesures,_Mesures[In]=1)

I have this error:

Calculation error in measure '_Mesures'[Measure]: The function SUMX cannot work with values of type Boolean.

[lbendlin]

That's not what I meant - sorry for being unclear. Your Total value for your [ln] measure needs to take into account that you have multiple rows in you filter context. That's where you need to do the SUMX, not in a new measure.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with.

[Madxfr]

Here the sample data

Excel Data 

Thanks

[lbendlin]

"If (Actual Etat=Exploitable  and (Pev Etat = Inexploitable or Pev Etat=Non contractualisé)) Then flag = 1"

Based on what?  What is the identifier that ties the rows together between dates? Do you have a primary key of sorts? Is it the combination of Fk_Pv,Fk_Dp and Fk_ModeleCom ?

See attached for my version so far.

[Madxfr]

Back again,

I have a new needed.

I need to display new rows on the table and to count them as a gain if they are 'exploitable'.

And i don't have any idea how to do that.

@lbendlin i modified your Power Bi file

exp.pbix 

Here what it's done and what is expected.

Cordialy,

[lbendlin]

You cannot add new rows in Power BI. You can only take rows away (filter them).   That new line will have to be in its own visual.

[Madxfr]

I create a new message with a different way.

But still stucked.

[v-rzhou-msft · Accepted solution]

Hi @Madxfr ,

I download your sample file and I find you build a single select slicer by "Feuil1"[Date_Histo]. This will cause your problem. If you select 1/20/2022 in your slicer. It will filter "Feuil1" table to show only Fk_Pv which contain date 1/20/2022. Fk_Pv= 2000 doesn't contain date 1/20/2022, so you couldn't find 2000 in your table visual.

Here I suggest you to use unrelated calendar to build the slicer and create a measure to calculate Gain.

Prev Etat = 
VAR _SELECT = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(MAX(Feuil1[Statut]),FILTER(Feuil1,Feuil1[Date_Histo] = _SELECT))

Last Etat = CALCULATE(SELECTEDVALUE(Feuil1[Statut]),FILTER(ALL (Feuil1[Date_Histo]),Feuil1[Date_Histo]=[Date Max]))

M_Gain =
VAR _T =
    ADDCOLUMNS (
        SUMMARIZE (
            Feuil1,
            Feuil1[Fk_Pv],
            Feuil1[IsNac],
            Feuil1[NAT],
            "Prev", [Prev Etat],
            "Last Etat", [Last Etat]
        ),
        "Gain",
            IF ( [Prev] <> [Last Etat] && [Last Etat] = "Exploitable", 1, 0 )
    )
RETURN
    SUMX ( _T, [Gain] )

Result is as below.

Best Regards,
Rico Zhou

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

[Madxfr]

@v-rzhou-msft  Thanks à lot.

Your DAX for the gain it's what i need.

[Madxfr]

I found the solution.

I re-think the mesure, i add a new column with this formula :

Gain =

VAR myDate =CALCULATE(

MAX(DateHisto[Date_Histo]),

ALL(DateHisto)

)

VAR myFk_Pv = Patrimoine[Fk_Pv]

VAR myFk_Dp = Patrimoine[Fk_Dp]

VAR myFk_ModeleCom = Patrimoine[Fk_ModeleCom]

VAR myRes =

CALCULATE(

SELECTEDVALUE( Patrimoine[StatutId] ),

FILTER( ALL(Patrimoine) ,

myDate = Patrimoine[Date_Histo] &&

myFk_Pv = Patrimoine[Fk_Pv] &&

myFk_Dp = Patrimoine[Fk_Dp] &&

myFk_ModeleCom = Patrimoine[Fk_ModeleCom]

)

)

RETURN

if(myRes=1 && Patrimoine[StatutId]<=0,1,0)

no I can count my Gain.

I don't know if it's the right way but it's working.

Thanks for your help.