cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Resolver I

## 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:

1 ACCEPTED SOLUTION
MVP

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

Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

6 REPLIES 6
MVP

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())``````
Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

Resolver I

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)

MVP

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

Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

Resolver I

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?

MVP

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

Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

Resolver I

Works like a charm!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors