Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, and happy new year!!
I have 5 measurements to calculate the sum for:
This week
This week -1
This week -2
This week -3
This week -4
This week -5
With the change of year, the formula no longer works. This is the formula used by subtracting the necessary weeks in each measure:
=CALCULATE(SUM(QTY);FILTER(TABLE,WEEKNUM(TABLE.DATE;2)=WEEKNUM(TODAY();2)-1))
WEEKNUM(TODAY();2) = 2
WEEKNUM(TODAY();2)-1= 1
WEEKNUM(TODAY();2)-2=0
...
I need:
WEEKNUM(TODAY();2) -2 = 53
Do you know any solution?
Thank you
Hi @pferban
The easiest would be to create an additional column on your date table that has the weeknumber across years. Then you can use that column to find all those weeks by just subtracting 1, 2, etc. One option:
WeekyNumGlobal =
CALCULATE (
DISTINCTCOUNT ( DateT[Weeknumber] ),
DateT[Date] <= EARLIER ( DateT[Date] ),
ALL ( DateT )
)
This assumes you have a WeekNumber column in your date already. Create it if you do not
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@pferban , Not very clear, refer to my week blogs if they 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
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |