Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Evelien
Advocate I
Advocate I

DAX formula Parallelperiod for weeks

Hello,

 

For a table that shows values per week, I would like to add a column with the values of the previous week, so I can calculate a week-on-week change, and eventually a count of wins (positive changes) and losses (negative changes).  I wanted to use a DAX formula with PARALLELPERIOD.  However this is only possible for Years, Months and Days and not for Weeks.

 

Does anyone know the solution or a workaround to get the same result?

 

Thanks.

 

Kind regards,

Evelien

1 ACCEPTED SOLUTION

@Evelien

 

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 

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

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

@Evelien

 

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.

DAX formula Parallelperiod for weeks.JPG

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

@v-jiascu-msft

 

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:

 

 

KeywordDateAppRankRank_Last_Week
Keyword 18/05/2017App 1100 
Keyword 28/05/2017App 1200 
Keyword 38/05/2017App 1300 
Keyword 115/05/2017App 1400100
Keyword 215/05/2017App 1500200
Keyword 315/05/2017App 1600300
Keyword 122/05/2017App 1700400
Keyword 222/05/2017App 1800500
Keyword 322/05/2017App 1900600
Keyword 18/05/2017App 2150 
Keyword 28/05/2017App 2250 
Keyword 38/05/2017App 2350 
Keyword 115/05/2017App 2450150
Keyword 215/05/2017App 2550250
Keyword 315/05/2017App 2650350
Keyword 122/05/2017App 2750450
Keyword 222/05/2017App 2850550
Keyword 322/05/2017App 2950650

 

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

@Evelien

 

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 

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

@v-jiascu-msft

 

Hi Dale,

 

It works!

Thanks a million.

 

Evelien

@Evelien

 

Hi Evelien,

 

It's my pleasure. 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vanessafvg
Super User
Super User

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

 

 

 

 

 

 





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!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.