Forum Discussion

Mike_Mace's avatar
Mike_Mace
Resolver I
5 years ago
Solved

Matrix subtraction stop at current week

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?
 
Snip:
 

 

Need Help
  • mwegener's avatar
    mwegener
    5 years ago

    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 ()
    )
)

     

6 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    5 years ago

    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's avatar
      Mike_Mace
      Resolver I
      5 years ago

      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's avatar
        mwegener
        Most Valuable Professional
        5 years ago

        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 ()
    )
)