cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
win_toeknee Member
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)

 

calendar.png

 

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. 

 

weekly variance.png

 

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!

 

 

1 REPLY 1
Community Support Team
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:

10.JPG

 

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.