Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
idontexist
Helper I
Helper I

Repeating values

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 CodeDateFactPlanShould be plan
RRUM11601.08.2017173194194
RRUM11607.08.2017177 194
RRUM11608.08.2017   
RRUM11614.08.20173 194
RRUM11615.08.2017   
RRUM11621.08.20173 194
RRUM11628.08.20173 194
1 ACCEPTED SOLUTION
technolog
Super User
Super User

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.

View solution in original post

1 REPLY 1
technolog
Super User
Super User

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.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.