Dynamic column with period

[bapt69]

Hi,

I didn't find out a post about it although I think at least one people were face to face with this situation.

Exemple : 

Historique (customers with statuts in each period) "d_client_histo"

client_id	start_date	end_date	status
200	01/01/2020	01/06/2022	CLUB
200	01/06/2022	01/01/2100	OR
100	01/02/2021	01/01/2100	CLUB

I got table DATE with relations DATE[date]=>start_date and DATE[date]=>end_date (only one active obviously)

And I got a table with all the status with his level

If one customer pass by a level over the CLUB's level -> his status-type is VIP

I would like to get these results :

If I choose year=2022

client_id	statuts_type
100	CLUB
200	VIP

If I choose 2021

client_id	statuts_type
100	CLUB
200	CLUB

I tried this measure :

Max_statut = CALCULATE(

   MAX(d_client_histo[Niveau_statut]),

   USERELATIONSHIP(d_date[date],d_client_histo[date_debut]),

   USERELATIONSHIP(d_date[date],d_client_histo[date_fin]),

        FILTER(d_client_histo,

        d_client[client_id]=d_client_histo[client_id]

        &&d_client_histo[date_fin]<=MAX(d_date[date])

        &&d_client_histo[date_debut]>=MIN(d_date[date])))

Thank you all

[bapt69]

I did that :

Status_type =

VAR _RANGESTART =

    MIN (d_date[date])

VAR _RANGEEND =

    MAX (d_date[date])

VAR _STATUSLIST =

    CALCULATETABLE (

        VALUES (d_client_histo[Niveau_statut]),

        FILTER (

            ALLEXCEPT ( d_client_histo, d_client_histo[client_id] ),

            OR(OR(OR((d_client_histo[date_debut] <= _RANGESTART && d_client_histo[date_fin] >= _RANGESTART && d_client_histo[date_fin] <= _RANGEEND),

                (d_client_histo[date_debut] >= _RANGESTART && d_client_histo[date_fin] <= _RANGEEND)),

                (d_client_histo[date_debut] >= _RANGESTART && d_client_histo[date_debut] <= _RANGEEND && d_client_histo[date_fin] >= _RANGEEND)),

                (d_client_histo[date_debut] <= _RANGESTART && d_client_histo[date_fin] >= _RANGEEND))

        )

    )

VAR _COUNT =

    COUNTAX ( _STATUSLIST, d_client_histo[Niveau_statut] )

RETURN

    IF (AND( _COUNT >= 1,MAX(d_client_histo[Niveau_statut])>100), "VIP", "CLUB" )

That's good.

But I can't have a table like that (cause it's a measure) :

Status Type       Nb

VIP                    512

CLUB                 1532

[Anonymous · Accepted solution]

Hi @bapt69 ,

Here I suggest you to create an unrelated calendar table to help you calculate the status.

Data model:

Measure:

Status_Type = 
VAR _RANGESTART =
    MIN ( 'Calendar'[Date] )
VAR _RANGEEND =
    MAX ( 'Calendar'[Date] )
VAR _STATUSLIST =
    CALCULATETABLE (
        VALUES ( d_client_histo[status] ),
        FILTER (
            ALLEXCEPT ( d_client_histo, d_client_histo[client_id] ),
            d_client_histo[start_date] <= _RANGEEND
                && d_client_histo[end_date] >= _RANGESTART
        )
    )
VAR _COUNT =
    COUNTAX ( _STATUSLIST, [status] )
RETURN
    IF ( _COUNT = 1 && "CLUB" IN _STATUSLIST, "CLUB", "VIP" )

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.

[bapt69]

Alright,

First of all, thank you for your answer!

I customize your DAX with my fields :

Status_type =

VAR _RANGESTART =

    MIN (d_date[date])

VAR _RANGEEND =

    MAX (d_date[date])

VAR _STATUSLIST =

    CALCULATETABLE (

        VALUES (d_client_histo[Niveau_statut]),

        FILTER (

            ALLEXCEPT ( d_client_histo, d_client_histo[client_id] ),

            d_client_histo[date_debut] >= _RANGEEND

                || d_client_histo[date_fin] <= _RANGESTART

        )

    )

VAR _COUNT =

    COUNTAX ( _STATUSLIST, d_client_histo[Niveau_statut] )

RETURN

    IF ( _COUNT = 1 && 1000 IN _STATUSLIST, "VIP", "CLUB" )

Cause my field "statuts" is actually the status level (numeric)

I change the "<" and ">" (otherside). I want to exit the records wich have date_debut over the MAX(date) and the record which have date_fin under the MIN(date). So, I did right?

Question : the tables (histo and Date) HAVE TO be unrelated? or just no need to be related, anyway if it is?

Anyway, all my customers are "CLUB" and still be CLUB.

I'm gonna try to customize a little more to find out how to do it.