Forum Discussion
Switching between dates and columns
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
7 Replies
Syndicate_Admin ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.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 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.
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
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.
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?
