Table with measures in row/column

[bapt69]

Hi everyone,

I got fields and measures which we have to use in a table.

Table with my customers (with a client_id)

Table with status historic (with status, client_id and begin_date/end_date)

And I want to get the status of each customer for the selected month (BOMONTH) and last month

With these, I would like to create a table with status_m-1 on rows and status_m on columns and count(customers) in values

Measure to get the status on the last month (not very good but I hope to find without help) :

statut_club_m-1 = CALCULATE(

   MAX(d_client_histo[statut_club]),

        FILTER(d_client_histo,

        AND(d_client_histo[date_debut]<=MAX(d_date[date])-31,d_client_histo[date_fin]=MAX(d_date[date])-31)))

The expected result :

I don't know if I'm very clear. Tell me if needed

Thank you in advance,

Baptiste

[bapt69 · Accepted solution]

I've created a tablewhich is contained the statuts for each customer et for each month (only last 13months). I duplicate the import of this table in my dataset. I add a relationship between the client_id and that was done.

Thank you for your support. I notice all that you gave to me! I'll compare.

[v-jianboli-msft]

Hi @bapt69 ,

Based on your description,  I have created a simple sample:

Please try:

First create a calendar table:

Create a slicer with the calendar table:

Apply the measure to the visual level filter:

Flag = IF(OR(SELECTEDVALUE('Table'[date_debut (start_date)]) in SELECTCOLUMNS('Calendar',"Date",[Date]),SELECTEDVALUE('Table'[date_fin (end_date)]) in SELECTCOLUMNS('Calendar',"Date",[Date])),1,0)

Final output:

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @bapt69 ,

What does your raw data look like?

Also from the picture you provided, there are values between different Client IDs, how are they calculated?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[bapt69]

Sorry for the delay (i was out of the office - off days)

I was thinking working with my table "d_client_histo" (customer historic). This table is like that :

customer_id, date_debut (start_date), date_fin (end_date), statut_club (statuts in this period)

008DF805, 01/05/2022, 23/10/2022, OCP1

008DF805, 24/10/2022, 05/03/2023, OCP2

008DF805, 06/03/2023, 01/01/2100, OCP3

...

If I choose August2022 and March2023, my table would look like :

	OCP2
OCP1	1

If I choose Dec22 and April23, my table would look like :

	OCP3
OCP2	1

I think creating a table with customer_id, month (maybe 12 last monthes), status but I don't know if my problem will be resolved lol

Am I clearer?