cancel
Showing results for
Did you mean:
Frequent Visitor

current vs previous week count

Hi Everyone,
i have seen that dax doesnt have a previous week function, can someone assist in writing a measure that shows previous week counts to compare to that current week count as shown in the tables below.

The first table is a representation of the actual data and the second table is the desired outcome.

If anyone can assist in writing such a measure I would greatly apreciate it,

Thank you

Data table

Expected outcome

1 ACCEPTED SOLUTION
Solution Specialist

Var _maxWeekNum = max(weeknum)

return

Calculate(

countrows(table),

filter(

all(table),

table[weeknum] = _maxWeekNum - 1

)

)

2 REPLIES 2
Super User

@tatenda24 , Please check the solution by @vapid128 , The solution work well within a year. Across year week need week rank column. Prefer a date table

new columns

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

New measure examples
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Solution Specialist

Var _maxWeekNum = max(weeknum)

return

Calculate(

countrows(table),

filter(

all(table),

table[weeknum] = _maxWeekNum - 1

)

)

Announcements

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors