Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I want to repeat values as you can see in column "should be plan". It should be based on column "Fact". If in this column there are any values so plan should be repeated. "Plan" i have 1 value for whole month, so you can try to use logic with "first-day-of-month" = 1.
This i use, but it did not work: Matrix Plan = if([Fact]<>"";CALCULATE(sum('table1'[Plan]);'Date'[Day Of Month]="1";""))
DSR Code | Date | Fact | Plan | Should be plan |
RRUM116 | 01.08.2017 | 173 | 194 | 194 |
RRUM116 | 07.08.2017 | 177 | 194 | |
RRUM116 | 08.08.2017 | |||
RRUM116 | 14.08.2017 | 3 | 194 | |
RRUM116 | 15.08.2017 | |||
RRUM116 | 21.08.2017 | 3 | 194 | |
RRUM116 | 28.08.2017 | 3 | 194 |
Solved! Go to Solution.
To achieve this, you would want a logic that says "if there's a fact for this DSR Code within this month, then get the 'Plan' value for the first day of that month". Here's how to do it:
Create a calculated column or a measure to determine if there's a fact for the current DSR Code within the current month.
Create a measure that gets the 'Plan' value for the first day of the month if the above condition is true.
Let's break this down:
Measure to determine if there's a fact for the current month:
Has_Fact_This_Month =
IF(
COUNTROWS(
FILTER(
ALL('table1'),
'table1'[DSR Code] = EARLIER('table1'[DSR Code]) &&
MONTH('table1'[Date]) = MONTH(EARLIER('table1'[Date])) &&
YEAR('table1'[Date]) = YEAR(EARLIER('table1'[Date])) &&
NOT(ISBLANK('table1'[Fact]))
)
) > 0,
1,
0
)
Now, create a measure that fetches the 'Plan' value for the first day of the month if there's a fact:
Matrix Plan =
IF(
[Has_Fact_This_Month] = 1,
CALCULATE(
SUM('table1'[Plan]),
FILTER(
ALL('table1'),
'table1'[DSR Code] = EARLIER('table1'[DSR Code]) &&
'Date'[Day Of Month] = 1 &&
MONTH('table1'[Date]) = MONTH(EARLIER('table1'[Date])) &&
YEAR('table1'[Date]) = YEAR(EARLIER('table1'[Date]))
)
),
BLANK()
)
Add the "Matrix Plan" measure to your table, and it should give you the desired output.
Note: The use of the EARLIER function is meant to capture the current row's context while comparing it against the whole table.
To achieve this, you would want a logic that says "if there's a fact for this DSR Code within this month, then get the 'Plan' value for the first day of that month". Here's how to do it:
Create a calculated column or a measure to determine if there's a fact for the current DSR Code within the current month.
Create a measure that gets the 'Plan' value for the first day of the month if the above condition is true.
Let's break this down:
Measure to determine if there's a fact for the current month:
Has_Fact_This_Month =
IF(
COUNTROWS(
FILTER(
ALL('table1'),
'table1'[DSR Code] = EARLIER('table1'[DSR Code]) &&
MONTH('table1'[Date]) = MONTH(EARLIER('table1'[Date])) &&
YEAR('table1'[Date]) = YEAR(EARLIER('table1'[Date])) &&
NOT(ISBLANK('table1'[Fact]))
)
) > 0,
1,
0
)
Now, create a measure that fetches the 'Plan' value for the first day of the month if there's a fact:
Matrix Plan =
IF(
[Has_Fact_This_Month] = 1,
CALCULATE(
SUM('table1'[Plan]),
FILTER(
ALL('table1'),
'table1'[DSR Code] = EARLIER('table1'[DSR Code]) &&
'Date'[Day Of Month] = 1 &&
MONTH('table1'[Date]) = MONTH(EARLIER('table1'[Date])) &&
YEAR('table1'[Date]) = YEAR(EARLIER('table1'[Date]))
)
),
BLANK()
)
Add the "Matrix Plan" measure to your table, and it should give you the desired output.
Note: The use of the EARLIER function is meant to capture the current row's context while comparing it against the whole table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |