cancel
Showing results for
Did you mean:
Super User

## Dax - change values based on a date

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.

1 ACCEPTED SOLUTION
Super User

@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
``````

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

3 REPLIES 3
Super User

@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
``````

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Super User

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

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Super User

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors