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.
CURRENT PROBLEM: My current week / month will cut off when the beginning of the month falls on either the end or begining of the week.
- EX: March 1 falls on Friday = my data will calculate the week to only have 2 days.
- EX: March 31 falls on Sunday = my data will calculate the week to only have 1 day. (see calendar screenshot below for reference)
PROBLEM TO SOLVE: Is there a way in which I can structure my time intelligence / calendar table to where if I want to model by the week level, the months do not cut off and will continue to account for 7 days? This is causing my data model to show sudden dips when in reality, it is just a change in the month during the week. The sudden dips in my model are bothering my clients and I am struggling to find a solution on how I can remedy this. Please see screenshot below (specifically the white arrows). The white arrows point to the very end / beginning of the month.
I have a calendar table with the following:
- Month = FORMAT('Calendar'[Date], "MMM")
- Week = WEEKNUM('Calendar'[Date])
I am pretty sure this is how it should be set up but if I am not correct, please let me know. Any guidance or suggestions will be greatly appreciated!
hi, @Anonymous
Usually, People just put year and week together. You'd better do not put month and week together.
Or if you could try this way:
Add a month No and week No. column in Calendar table
Then use a measure like this
Measure 5 = CALCULATE ( SUM ( Table3[Qty] ), FILTER ( ALLSELECTED ( Table3 ), Table3[Month] = MAX ( Table3[Month] ) - 1 && Table3[Week] = MAX ( Table3[Week] ) ) ) + CALCULATE ( SUM ( Table3[Qty] ) ) + CALCULATE ( SUM ( Table3[Qty] ), FILTER ( ALLSELECTED ( Table3 ), Table3[Month] = MAX ( Table3[Month] ) + 1 && Table3[Week] = MAX ( Table3[Week] ) ) )
For example:
Best Regards,
Lin
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |