Matrix subtraction stop at current week

[Mike_Mace]

Happy lockdowning Forum,

I have a matrix with info:

1. A list of 25 deliverables with random plan dates and random dates they were submitted

2. I created (head) columns by "starting-date" week. All random dates are grouped in those weeks

3. To measure the missed items i created measure

failed = SUM('plan date'[plan sum])-Sum('submission date'[sub sum])

Notice on snip that subtraction takes place on all weeks, including future ones. 

I need subtraction to stop at week before current.

I thought about having an if on subsum >0 but that hasnt worked  (measure failed 2 on pbi file)

Ideas?

File@ https://we.tl/t-27W3zjjjFp

Snip:

 

[mwegener]

Hi @Mike_Mace ,

try this

failed = 
IF(MAX('Date Calendar'[Week start]) < TODAY()  - WEEKDAY(TODAY(),2) +1,
SUM('plan date'[plan sum])-Sum('submission date'[sub sum]), BLANK())

[Mike_Mace]

Hi @mwegener 

That works well, it stops subtraction before running week thanks. 

Although it doesnt give Grand Totals for this measure. Any idea why? (named "failed 2" on snip below)

[mwegener]

Hi @Mike_Mace ,

because the total does not filter to the start of the week.

Try this. 

failed =
SUMX (
    VALUES ( 'Date Calendar'[Week start] ),
    IF (
        MAX ( 'Date Calendar'[Week start] )
            < TODAY () - WEEKDAY ( TODAY (), 2 ) + 1,
        SUM ( 'plan date'[plan sum] ) - SUM ( 'submission date'[sub sum] ),
        BLANK ()
    )
)

[Mike_Mace]

Hi @mwegener 

It looks like new "failed 3" has same results as "failed 2". I gave them a different highlight on the snip to read with ease. Still no row/grand totals
Is there a way around this or do i need to rethink model structure?

[mwegener · Accepted solution]

Sorry, I fooled myself.

failed 2 = 
SUMX (
    VALUES ( 'Date Calendar'[Week start] ),
    IF (
        'Date Calendar'[Week start]
            < TODAY () - WEEKDAY ( TODAY (), 2 ) + 1,
        CALCULATE(SUM ( 'plan date'[plan sum] ) - SUM ( 'submission date'[sub sum] )),
        BLANK ()
    )
)

[Mike_Mace]

@mwegener 

Works like a charm!