cancel
Showing results for
Did you mean:
Highlighted
Member

## Time Intelligence: Weekly / Monthly Data Model Help Needed

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!

Community Support Team

## Re: Time Intelligence: Weekly / Monthly Data Model Help Needed

hi, @win_toeknee

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

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