Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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!

 

 

Need Help
Tutorial Requests

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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