Switching between dates and columns

[Syndicate_Admin]

Hello community!

I need your help because I have a problem that is not solved, I have tried nesting IF functions but I do not get a result, I show you below an example of the table that I have to count with 12k records and is updated daily.

I would need a measurement that would tell me if the value changes between columns and days and how much value moves between columns, knowing that every month I have one more column and that every day rows are added.

Thanks in advance

[Syndicate_Admin]

Hello, back to the matter:

In the end using the method of "Unpivot columns" I achieved this:

Do you think it is possible to get a measure that indicates whether there has been a change in the "attributte" column and that returns the value of the "value" column only once? Considering that the original table has thousands of rows...

Thank you

[v-stephen-msft]

Hi @JepGr ,

Add a index column in Power QuerySort in the following order in Power Query, and then add an index column.

Then you can create a measure to show the change of 'Attribute' and return the value of the 'value'.

Measure = IF(MAX('Table'[Attribute])<>CALCULATE(MAX('Table'[Attribute]),FILTER(ALL('Table'),[Index]=MAX('Table'[Index])-1)),SUM('Table'[Value]))

Best Regards,

Stephen Tao

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

[Syndicate_Admin]

Hi Sthepen!

That works!!! Thank you very much! I'm trying to add more filters to the measure (without success) in order to only run when the Sales order is the same, so in this situation the measure is not working because this is a different sales order:

I try with the earlier fx inside the Calculate:

What I'm doing wrong?

[amitchandak]

@Syndicate_Admin ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Syndicate_Admin]

Of course you do, thank you very much

Exit:

Sales document	Moved to 22/02	Moved to 22/03	Moved to 22/04	Moved to 22/05	Moved to 22/06	Moved to 22/07	Moved to 22/08	Moved to 22/09	Moved to 22/10	Moved to 22/11	Moved to 22/12
7788596	0	0	14415	0	0	0	0	0	0	0	0

Entrance:

Sales document	Open 22/01	Open 22/02	Open 22/03	Open 22/04	Open 22/05	Open 22/06	Open 22/07	Open 22/08	Open 22/09	Open 22/10	Open 22/11	Open 22/12	Data Snapshot
7788596		14415,5											31/01/2022
7788596		14415,5											01/02/2022
7788596		14415,5											02/02/2022
7788596		14415,5											03/02/2022
7788596		14415,5											04/02/2022
7788596		14415,5											07/02/2022
7788596				14415,5									08/02/2022
7788596				14415,5									10/02/2022
7788596				14415,5									11/02/2022

[v-stephen-msft]

Hi @JepGr ,

It is suggested to unpivot the columns to one date column.

You can select the Sales document column and select Unpivot Other Columns.

Then you can convert the Attribute column to the YearMonth column.

Hope it helps.

Best Regards,

Stephen Tao

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

[JepGr]

Hi Stephen,

Yes, this could work, but I have this:

And wants the Moved by month, sorry if I made a mistake with the explanation

KR

J