How to calculate difference in value from 2 rows that are identified by 2 columns?

[Anonymous]

Hi, 

I'm trying to do up a report to show quarterly changes in a metric that I'm tracking. As seen in the table below, each row represents the results ('Quality' and 'Short-term Quantity) from a country in a quarter (i.e. Date). 

Date	Country	Quality
31-Mar-21	A	0.5
31-Mar-21	B	0.43
30-Jun-21	A	0.22
30-Jun-21	B	0.5

I would then like to find out each country's difference in results, quarter over quarter. So that it looks like something like this:

Date	Country	Quality	Short-term Quantity	Difference in Quality (measured against previous Quarter)
31-Mar-21	A	0.5	0.54	n.a.
31-Mar-21	B	0.43	0.43	n.a.
30-Jun-21	A	0.22	0.36	-.28
30-Jun-21	B	0.5	0.54	.07

I plan to have my data refreshed every quarter, so as a new quarter comes along, there will be new datapoints to reflect the results of the country in that new quarter. Currently struggling to even find out where to begin this or if this is even doable. Any help is greatly appreciated!! 

[Anonymous]

Hi @Anonymous,

Did DataInsights 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept this suggestion to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[DataInsights · Accepted solution]

@Anonymous,

Create a date table with column Qtr End Date, and create a relationship between the date table and fact table:

You can use the calculated column below in the date table:

Qtr End Date = ENDOFQUARTER ( DimDate[Date] )

Create measure:

Quantity Difference in Quality = 
VAR vCurrentQtr =
    MAX ( FactTable[Quality] )
VAR vLastQtr =
    CALCULATE ( MAX ( FactTable[Quality] ), DATEADD ( DimDate[Date], -1, QUARTER ) )
VAR vResult =
    IF (
        NOT ( ISBLANK ( vCurrentQtr ) || ISBLANK ( vLastQtr ) ),
        vCurrentQtr - vLastQtr
    )
RETURN
    vResult

Create visual using DimDate[Qtr End Date]: