Weekly Calculation

[Anonymous]

Hi,

I need to calculate weekly amount. Actually table is filtered for "This Week". 
Thing is if a new month is starting in the middle of a  week, we need to see only the value for new months for that particular week.
For eg,  Now this week started from Aug 30 and will end on Sept 5. 
During this week calculation I only need to calculate, the amount / value from Sept 1 to Sep 5. Don't need to consider Aug 30, & 31.
How can I apply this condition in measure. 

Can anyone help me pls? Thanks in advance.

[lbendlin]

@Anonymous 's post demonstrates why an external predefined calendar table is better 99% of the time. 

[Anonymous · Accepted solution]

Hi @Anonymous 

Try my code to build a date table. If you use weeknum, you will get confused at the begining of next year.

For example, 2020/12/31 and 2021/01/01 are in the same week, but weeknum will show you 53 and 1. That's incorrect and will make our calculate difficult.

Date table:

Date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Weeknum", WEEKNUM ( [Date], 2 ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Add calculated columns:

ISO 8601 WeekNum = 
VAR _COUNT0 =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[WeekNum] - 1 = 0
        )
    )
VAR _BASENUM1 =
    IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
VAR _ISO_8601_WeekNum =
    IF (
        WEEKDAY ( DATE ( 'Date'[Year] - 1, 01, 01 ) ) <> 1
            && 'Date'[Year] = 'Date'[Year]
            && _BASENUM1 = 0,
        WEEKNUM ( DATE ( MIN ( 'Date'[Year] ), 12, 31 ), 1 ) - 1,
        _BASENUM1
    )
RETURN
    _ISO_8601_WeekNum

ISO_Year = 
VAR _COUNT0 =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[WeekNum] - 1 = 0
        )
    )
VAR _BASENUM1 =
    IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
RETURN
IF(_BASENUM1 = 0,'Date'[Year] -1,'Date'[Year])

Then build a relationship between Date table and Fact table by Date columns.

Then create a measure to get result.

This Week =
VAR _Today =
    TODAY ()
VAR _TodayISOYear =
    CALCULATE ( SUM ( 'Date'[ISO_Year] ), FILTER ( 'Date', 'Date'[Date] = _Today ) )
VAR _TodayISOWeeknum =
    CALCULATE (
        SUM ( 'Date'[ISO 8601 WeekNum] ),
        FILTER ( 'Date', 'Date'[Date] = _Today )
    )
VAR _MaxYearMonth =
    CALCULATE (
        MAX ( 'Date'[YearMonth] ),
        FILTER (
            'Date',
            'Date'[ISO_Year] = _TodayISOYear
                && 'Date'[ISO 8601 WeekNum] = _TodayISOWeeknum
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Date',
            'Date'[YearMonth] = _MaxYearMonth
                && 'Date'[ISO_Year] = _TodayISOYear
                && 'Date'[ISO 8601 WeekNum] = _TodayISOWeeknum
        )
    )

My Sample is from 2020/12/30 to 2021/9/16. So here I will show you 3 situations.

1. If today is 2021/09/06, result is 5+4+0+3+3+4+3 = 22.

2. If today is 2021/08/30, result is from 2021/09/01 to 2021/09/05 :1+4+9+0+3 = 17.

 

3. If today is 2020/12/30, result is from 2021/01/01 to 2021/01/03 :1+0+5 = 6.

Best Regards,
Rico Zhou

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

[lbendlin]

You should use a Calendar table for that, with week number and month number columns.