MTD for fiscal calendar and selected week

[joshua1990]

Hi all!

I have a report with a filter on top to select a year-week combination.

Then I have a KPI visual that sums up the sales.

Now I would like to select a year-week and to sum up every sale for the corresponing month based on the year-week.

This approach is not working, I still get the sum for the corresponing week and not the full month:

Sales MTD = 
CALCULATE (
    [Sales],
    FILTER (
        'Calendar',
        'Calendar'[Year] = VALUES ( 'Calendar'[Year] )
            && 'Calendar'[ Month Num] = MAX ( 'Calendar'[Month Num] )
            && 'Calendar'[Week] <= MAX ( 'Calendar'[Week] )
    )
)

[FreemanZ · Accepted solution]

hi @joshua1990 

what context do you have for the measure?

try like:

Sales MTD = 
CALCULATE (
    [Sales],
    FILTER (
        ALL( 'Calendar') ,
        'Calendar'[Year] = VALUES ( 'Calendar'[Year] )
            && 'Calendar'[ Month Num] = MAX ( 'Calendar'[Month Num] )
            && 'Calendar'[Week] <= MAX ( 'Calendar'[Week] )
    )
)