A measure or a column that subtract a specific row with conditions from current row

[learninPBI5Hard]

I would like to create a new measure or a new column that calculates the difference of current row to a specific row. So the logic is that each row to subtract the row with same company, company name and same year Q1. 
if the row is "co001" && "companyA" && "co001&companyA" && [Quarter] = "2021 Q2" then return 90-100, where 100 is the same company in "2021 Q1"
if the row is  "co001" && "companyA" && "co001&companyA" && [Quarter] = "2021 Q1" then pass to next row for new calculation
if current row is "2022 Q2" then "2022 Q1" of the same company does not exist, return null

Below tables are examples of what I working on, [sale_difference] is the column output I desire. 

Table1:

Company	company name	Quarter	co&co_name	sale_amt	sale_difference
co001	companyA	1	co001&companyA	100	null
co002	companyJ	1	co002&companyJ	98	null
co003	companyQ	2	co003&companyQ	97	1
co004	companyH	2	co004&companyH	100
co001	companyA	2	co001&companyA	90	-10
co001	companyA	5	co001&companyA	95	null
co002	companyJ	1	co002&companyJ	92	92
co004	companyH	5	co004&companyH	99	null
co004	companyH	6	co004&companyH	89	-10
co003	companyQ	1	co003&companyQ	96	96

Table2:

QuarterID	Quarter_Name
1	2021 Q1
2	2021 Q2
3	2021 Q3
4	2021 Q4
5	2022 Q1
6	2022 Q2
7	2022 Q3
8	2022 Q4

--get table2 quarter name based on quarter id in table1
VAR _targetQ = 
LOOKUPVALUE('Table2'[quarter_name], 
            'Table2'[quarterID], 
            'Table1'[Quarter])

--get quarter name as in "yyyy Q1"
VAR _targetQCri = 
CONCATENATE (LEFT(_targetQ , 4), " ", "Q1")

--get quarter ID for table1 lookup
VAR _targetQID = 
LOOKUPVALUE('Table2'[QuarterID], 
            'Table2'[quarter_name], 
            _targetQCri)

--get current row's year Q1 sale amount
VAR _targetQSale = 
LOOKUPVALUE('Table1'[sale_amt], 
            'Table1'[Company], 
            'Table1'[Company],
            'Table1'[company name], 
            'Table1'[company name], 
            'Table1'[co&co_name], 
            'Table1'[co&co_name], 
            'Table1'[Quarter],
            _targetQID
)
 
RETURN 
/*if current row quarterID contains "Q1", go to next row, else the current row minus the same year Q1*/
IF ( CONTAINSSTRING(_targetQ , "Q1") , pass, 
     CALCULATE(
     'Table1'[sale_amt] - _targetQSale
     )

I have above code, but it seems not working and I dont know how to get this in a loop for each row to perform the calculation in a measure. Thank you in advance. 

[parry2k]

@learninPBI5Hard use TI functions:  

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Time Intelligence Playlist

[learninPBI5Hard]

Im sorry for the confusion. I understand that q1 can be translated as other value type like time or number, but my actual data cannot be translated to time. The data is more like "2022 normal", "2022 interestRate down", or "2022 int volitility up". So the logic is like different scenario impacts from 'normal' of the year.  

[parry2k]

@learninPBI5Hard solution is attached, tweak it as you see fit. The best method though is to convert Quarter to end of the quarter date and then use time intelligence functions, although the attached solution is based on your sample data (not using TI functions)

[learninPBI5Hard]

I have a sample file, I cannot find the upload portal for that. is there any other way to share my file? 

[learninPBI5Hard · Accepted solution]

It seems like the code is only good for one quarter before the current quarter. I should've include q3 and q4 so that 2022 q3 can subtract 2022 q1 as an example. I developed below code but it's only good for column setup. Is it possible to make it a measure? why measure does not do lookupvalue like column?

sale_exclude_q1 = 
VAR _corrQ1 = 
LOOKUPVALUE(Table1[sale_amt], 
    Table1[co&co_name], 
    Table1[co&co_name],
    Table1[Quarter], 
    LOOKUPVALUE(
        Table2[QuarterID], Table2[Quarter_Name], 
        CONCATENATE(
            LEFT(
                LOOKUPVALUE(Table2[Quarter_Name], Table2[QuarterID], Table1[Quarter])
                ,4
            ), " Q1"
        )
    )
)

RETURN 
IF(OR(CONTAINSSTRING(LOOKUPVALUE(Table2[Quarter_Name], Table2[QuarterID], Table1[Quarter]), " Q1"), _corrQ1 = BLANK()), 
    BLANK(),
    Table1[sale_amt] - _corrQ1
)

[foodd]

Please create a pbix file that contains some sample data but still reflects your data model (tables, relationships, calculated columns, and measures), upload the pbix to Onedrive or Dropbox, and share the link. Please use Excel to create the sample data instead of the manual input method share the xlsx as well.

Describe the expected results based on the sample you provide.