Showing changes in a value over time in a table columnwise when the source data is spread over rows

[zb134]

Sorry if the title doesn't make sense. I think the tables below should clear it up:

Source Table:

(Note that in the actual dataset there are 30+ other columns and close to 300 rows that cover multiple quarters)

Item	ValueX	Date
123	Lvl 1	2022 Q3
213	Lvl 1	2022 Q3
312	Lvl 2	2022 Q3
123	Lvl 2	2022 Q4
213	Lvl 1	2022 Q4
312	Lvl 3	2022 Q4

What I want to show in the table visualization is only the items that have had a change in ValueX over the previous quarter. So using the above example it should look like:

Item	ValueX Previous Quarter	ValueXCurrent
123	Lvl1	Lvl2
312	Lvl2	Lvl3

Item 213 would be excluded since it did not have a change in value X.

Any suggestions are highly appreciated!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[zb134]

Hi Ashish,

First of all thanks a lot. Took a bit of tweaking to figure out what you did and apply it to my use case but it worked wonderfully! Super grateful for you help.

Now, if I may, can I ask a follow-up clarification:

You used the calculation: 

Value in previous quarter = CALCULATE([Value],PREVIOUSQUARTER('Calendar'[Date])) 

Why is there the need to create the 'Calendar' table?

I tried replicating the function using the existing date field in the Data table but it doesn't work

Value in previous quarter = CALCULATE([Value],PREVIOUSQUARTER(Data[Date]))

What makes the Calendar.Date work but not the Data.Date?

I also tried using

 Value in previous quarter = CALCULATE([Value],'Data'[Date] IN { DATE(2022, 9, 1) }

This worked somewhat but would not allow fo the use of slicers and obviously isn't as useful as your function since it reference a static date that will have to be change all the time.

Just trying to learn!

Thanks!!!!!!
)

[Ashish_Mathur]

You are welcome.  A Calendar table allows the usage of Date and Time Intelligence functions and allows you to create relationships with other Fact tables (which have a Date column).  The reason (probably) Data[Date] does not work is that for Date and Time Intelligence functions to work, there should be a continuous range of dates.