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 wee...
Need Help
Tutorial Requests
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