Dax - change values based on a date

[EmaVasileva]

Hi team,

I need help with a dax calculation. I have the following two tables:

Date1	Shop	Amount	SUM of Amount by locations
01.12.2021	ABC-1	5.5	5.5
06.12.2021	ABC-1	4.5	10 (5.5+4.5)
10.12.2021	ABC-1	5	15 (5.5+4.5+5)
01.12.2021	DEF-2	6	6
05.12.2021	JJJ-3	10	10
17.12.2021	JJJ-3	5	15

Shop	Date2
ABC-1	8.12.2021
JJJ-3	15.12.2021

In both tables the Shop names are the same. Date2 is different than Date1.
I need a calculation/new column with Fixed amount: whenever we have a date2 for some shop, I need the “Amount” until date2 (for the specific location) to become 0.

Example for ABC-1: The fixed amount column should show 0 until 08.12.2021 (DATE2), after this date, the fixed amount should show the number equal to the number in Amount:

Date1	Shop	Amount	Fixed amount
01.12.2021	ABC-1	5.5	0
06.12.2021	ABC-1	4.5	0
10.12.2021	ABC-1	5	5

 
For DEF-2 we don’t have a Date2 field, so the Fixed amount will be 6 (the same as in the first table)

For JJJ-3 the fixed amount should be 0 until 15.12.2021 Date2), and 5 after 15.12.2021

Date1	Shop	Amount	Fixed amount
05.12.2021	JJJ-3	10	0
17.12.2021	JJJ-3	5	5

There are a lot of Shops, so we cannot hardcode them in the formula.

I will appreciate any help. Thank you.

[smpa01 · Accepted solution]

@EmaVasileva  try this measure

Measure =
VAR _dt1 =
    MAX ( 'Table 1'[Date1] )
VAR _dt2 =
    CALCULATE (
        MAX ( 'Table 2'[Date2] ),
        CROSSFILTER ( 'Table 1'[Shop], 'Table 2'[Shop], BOTH )
    )
VAR _amt =
    MAX ( 'Table 1'[Amount] )
VAR _comp =
    IF ( _dt2 <> BLANK () && _dt1 > _dt2, _amt )
RETURN
    _comp

[smpa01]

@EmaVasileva  did you have a chance to try this yet?

[vanessafvg]

can i ask what you are actual trying to do from a business perspective what is the requirement.

There are many ways to handle this data from a modelling perspective.

I would potentially apped the tables in powerquery.   However that depends on what your end game is.

If you merged the tables,

first you would add a new column to both tables,

to table 1 with date1 you add a flag column = 0

the table with date2, you could add flag column= 1

then when you append the tables you will know where to start, however reading through the examples you have provided it does not make sense.

I do not see any date with 08.12.2021