Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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 ()
)
)
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."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
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())
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."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
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)
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 ()
)
)
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."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
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?
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 ()
)
)
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."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |