cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate 14 days running maximum competitor price for each individual competitor in DAX

Given that I have competitor name, competitor price & date columns, I have to calculate 14 days running maximum competitor price for each individual competitor in DAX

Sample table: (input)

 Product ID Competitor Name Competitor Price Date 508861 XYZ.dk 139kr 13/09/21 508861 ABC.dk 125kr 13/09/21 508861 ABC.dk 129kr 14/09/21 508861 ABC.dk 119kr 14/09/21 508861 XYZ.dk 109kr 14/09/21 508861 ABC.dk 119kr 15/09/21 508861 ABC.dk 119kr 16/09/21 508861 ABC.dk 139kr 17/09/21 508861 ABC.dk 119kr 18/09/21 508861 ABC.dk 119kr 19/09/21 508861 XYZ.dk 119kr 21/09/21 508861 XYZ.dk 119kr 21/09/21 508861 XYZ.dk 119kr 21/09/21 508861 XYZ.dk 109kr 22/09/21 508861 XYZ.dk 109kr 24/09/21 508861 ABC.dk 109kr 26/09/21 508861 ABC.dk 129kr 28/09/21 508861 ABC.dk 119kr 29/09/21 508861 XYZ.dk 139kr 30/09/21

Expected Output:

 Product ID Competitor Name Competitor Price Date Max price 508861 XYZ.dk 139kr 13/09/21 508861 ABC.dk 125kr 13/09/21 508861 ABC.dk 129kr 14/09/21 508861 ABC.dk 119kr 14/09/21 508861 XYZ.dk 109kr 14/09/21 508861 ABC.dk 119kr 15/09/21 508861 ABC.dk 119kr 16/09/21 508861 ABC.dk 139kr 17/09/21 508861 ABC.dk 119kr 18/09/21 508861 ABC.dk 119kr 19/09/21 508861 XYZ.dk 119kr 21/09/21 508861 XYZ.dk 119kr 21/09/21 508861 XYZ.dk 119kr 21/09/21 508861 XYZ.dk 109kr 24/09/21 508861 XYZ.dk 109kr 24/09/21 508861 ABC.dk 109kr 26/09/21 508861 ABC.dk 129kr 28/09/21 508861 ABC.dk 119kr 29/09/21 139kr 508861 XYZ.dk 139kr 30/09/21 139kr

Current DAX code: (not working as wished to)

``````Max price =
MAXX(DATESINPERIOD('calendar'[Date], LASTDATE('calendar'[Date]),-14,DAY),competitor_v2[CompetitorPrice])``````

Also tried this:

``Max price last 14 days = CALCULATE(max(competitor_v2[CompetitorPrice]), TODAY()-14 > competitor_v2[Date])``

Note: There is no explicit relation with any other table than the (Calendar) date table. (Considering that calendar[date] & competitor_v2[date] is same)

Would really appreciate it if anyone can reflect on what is that I could be missing. 🙂

1 ACCEPTED SOLUTION
Community Support

Your question is whether you only want the last date to display the maximum value?

You can add conditions to judge the date in the original formula.

``````Max price =
IF (
[Date]
= CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( 'Table', [Competitor Name] = EARLIER ( 'Table'[Competitor Name] ) )
),
IF (
'Table'[Date] - MIN ( 'Table'[Date] ) < 14,
BLANK (),
MAXX (
FILTER (
'Table',
[Date]
>= EARLIER ( 'Table'[Date] ) - 14
&& [Date] <= EARLIER ( 'Table'[Date] )
),
'Table'[Competitor Price]
)
),
BLANK ()
)
``````

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Community Support

You can try the following methods.

``````Max price =
IF (
[Date] - MIN ( 'Table'[Date] ) < 14,
BLANK (),
MAXX (
FILTER (
'Table',
[Date]
>= EARLIER ( 'Table'[Date] ) - 14
&& [Date] <= EARLIER ( 'Table'[Date] )
),
'Table'[Competitor Price]
)
)``````

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Hi @v-zhangti, thanks for trying it out for me, I actually tried with my data, and apparently is not working as expected.

Any further guidance is appreciated. 🙂

Community Support

Your question is whether you only want the last date to display the maximum value?

You can add conditions to judge the date in the original formula.

``````Max price =
IF (
[Date]
= CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( 'Table', [Competitor Name] = EARLIER ( 'Table'[Competitor Name] ) )
),
IF (
'Table'[Date] - MIN ( 'Table'[Date] ) < 14,
BLANK (),
MAXX (
FILTER (
'Table',
[Date]
>= EARLIER ( 'Table'[Date] ) - 14
&& [Date] <= EARLIER ( 'Table'[Date] )
),
'Table'[Competitor Price]
)
),
BLANK ()
)
``````

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Hi @v-zhangti, thank you so much for your inputs and for practically trying it out for me, I'll give it a run for my data set and see if runs smoothly for me and gets me the desired outcome. 😁

Best,

Regular Visitor

Hi @VahidDM, thank you so much for the comment, I will quickly try to update my post according to suggestions. Much appreciated..!

Community Champion

Can you post sample data as text and expected output?
Not enough information to go on;

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.