Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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). 

 

DateCountryQuality
31-Mar-21A0.5
31-Mar-21B0.43
30-Jun-21A0.22
30-Jun-21B0.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:

DateCountryQualityShort-term QuantityDifference in Quality (measured against previous Quarter)
31-Mar-21A0.50.54n.a.
31-Mar-21B0.430.43n.a.
30-Jun-21A0.220.36-.28
30-Jun-21B0.50.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!! 

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    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]:

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    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]:

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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