Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I hope someone could please help me with this report im working on. I need to get the sum of each week for the last 4 weeks. It was still working well last year and i was using these formulas but since the begining of this year its not:
The totals right now are supposed to be WK1= 1195 =, WK2 = 26, WK3 = 2203 and WK4 = 2386
I have a Calendar Table
I'm using direct query
Thank you!
Solved! Go to Solution.
@atjt217 ,
According to my understanding, the formula should be like this,
prevWEEK4 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -3 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
__weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 4, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum-4 ) )
)
prevWEEK3 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -3 ) )
)
prevWEEK2 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -2 ) )
)
prevWEEK1 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -1 ) )
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@atjt217 ,
According to my understanding, the formula should be like this,
prevWEEK4 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -3 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
__weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 4, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum-4 ) )
)
prevWEEK3 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -3 ) )
)
prevWEEK2 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -2 ) )
)
prevWEEK1 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -1 ) )
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It works. Thank you so much for your help!
Hi @atjt217 ,
Please try this formula again.
prevWEEK1 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
IF(
__weekNum = 1,
CALCULATE(
[Billed_total],
FILTER(
DateDimension,
[Year] = __year - 1 && [Week_Number] = __maxWeekNum
)
),
CALCULATE(
[Billed_total],
FILTER(
DateDimension,
[Week_Number]= __weekNum - 1
)
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, Thank you for the reponse. It was working for Weeks 1-3 but week 4 is returning blank.
For week 4 here is what i used:
Hi @atjt217 ,
When WEEKNUM(TODAY(), 1) is less than or equal to 4, the previous one week, two weeks, three weeks, and four weeks should be the last few weeks of the previous year.
Such as current week number is 1:
prevWEEK1 =
VAR __weekNum = WEEKNUM(TODAY(), 2)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(Sheet5), [Year] = __year - 1),
[WeekNum]
)
RETURN
IF(
__weekNum = 1,
CALCULATE(
SUM(Sheet5[Value]),
FILTER(
Sheet5,
[Year] = __year - 1 && [WeekNum] = __maxWeekNum
)
),
CALCULATE(
SUM(Sheet5[Value]),
FILTER(
Sheet5,
[WeekNum] = __weekNum - 1
)
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Lionel,
I tried to use the formula:
@atjt217 , refer my blog , have additional column on week rank in you date table and use those. If needed; you can have date table in power bi even in direct query mode
See if this can help
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Date table in power bi in Direct query https://www.youtube.com/watch?v=cQfJ0GmQ5os&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&index=7
I tried the formula on your blog as a calculated measure:
But im getting an error that the syntax ASC is incorrect.
Could you please tell me if i happen to miss anything?
Thanks for you help amitchandak!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |