DAX formula Parallelperiod for weeks

Hi Evelien,

Try this formula please. It's a calculated column.

NewColumn =
VAR LastWeek =
    CALCULATE (
        MAX ( 'Table1'[Date] ),
        FILTER (
            'Table1',
            'Table1'[Date] < EARLIER ( 'Table1'[Date] )
                && 'Table1'[App] = EARLIER ( Table1[App] )
                && 'Table1'[Keyword] = EARLIER ( 'Table1'[Keyword] )
        )
    )
RETURN
    CALCULATE (
        MIN ( 'Table1'[Rank] ),
        FILTER (
            'Table1',
            'Table1'[Date] = LastWeek
                && 'Table1'[App] = EARLIER ( Table1[App] )
                && 'Table1'[Keyword] = EARLIER ( Table1[Keyword] )
        )
    )

Best Regards!

Dale

6 Replies

vanessafvg
Community Champion
9 years ago
not sure if this helps at all https://community.powerbi.com/t5/Desktop/Create-Previous-Week-Filter/td-p/9492

Microsoft Employee

Hi,

How does your table look like? If you had one like it in the picture, you could have two options to get it done.

Option 1: If your table had a column WeekNum, you can sort the table with WeekNum. If not, you can add an index as WeekNum. First sort the table by date, then add an index. Finally, you can use this formula as a calculated column.

Option_1 =
CALCULATE (
    MIN ( Table1[Amount] ),
    FILTER ( 'Table1', 'Table1'[Weeknum] = EARLIER ( 'Table1'[Weeknum] ) - 1 )
)

Option 2: If you used a date to act as a week, you could use this formula as a calculated column.

Option_2 =
VAR LastWeek =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) )
    )
RETURN
CALCULATE ( MIN ( Table1[Amount] ), FILTER ( Table1, Table1[Date] = LastWeek ) )

Please don’t worry about Min function. Actually there is only one value when we use Min due to running context.

Best Regards!

Dale

Advocate I

Thanks for the suggestions. This seems to be what I am looking for.

However, something I forgot to mention, I would need to know the last week value not only by date, but also by 2 other parameters: Keyword and App.

Please find below the table to see what I mean and what I am after:

Keyword	Date	App	Rank	Rank_Last_Week
Keyword 1	8/05/2017	App 1	100
Keyword 2	8/05/2017	App 1	200
Keyword 3	8/05/2017	App 1	300
Keyword 1	15/05/2017	App 1	400	100
Keyword 2	15/05/2017	App 1	500	200
Keyword 3	15/05/2017	App 1	600	300
Keyword 1	22/05/2017	App 1	700	400
Keyword 2	22/05/2017	App 1	800	500
Keyword 3	22/05/2017	App 1	900	600
Keyword 1	8/05/2017	App 2	150
Keyword 2	8/05/2017	App 2	250
Keyword 3	8/05/2017	App 2	350
Keyword 1	15/05/2017	App 2	450	150
Keyword 2	15/05/2017	App 2	550	250
Keyword 3	15/05/2017	App 2	650	350
Keyword 1	22/05/2017	App 2	750	450
Keyword 2	22/05/2017	App 2	850	550
Keyword 3	22/05/2017	App 2	950	650

I hope it is clear.

Do you think in some way it will be possible to adjust your proposed formula to this?

Many thanks for your help!

Kind regards,

Evelien

Microsoft Employee