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

[Anonymous]

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

[v-zhangti]

Hi, @Anonymous 

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.

[Anonymous]

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

Please find the image below (Please refer the Maximum Price column)

Any further guidance is appreciated. 🙂

[v-zhangti · Accepted solution]

Hi, @Anonymous 

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.

[Anonymous]

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,
Prasad

[Anonymous]

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

[VahidDM]

Hi @Anonymous 

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

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/