cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Replicated a value from the last month, if the value is missing in the next month

I have a table in power bi

Date           Size   name

1.1.2024      10     v1

10.1.2024     20    v1

2.02.2024     15    v1

20.3.2024     25    v1

1.1.2024        3     v2

10.1.2024      5     v2

2.02.2024      7     v2

20.3.2024      9     v2

1.1.2024        33 v1

2.11.2023      11 v3

and another calculated table

NewTable = CALENDAR (MIN(SourceData[Date]), MAX(SourceData[Date]))

Calculated table has calculated columns

CorrectDateToCheckVolume =

VAR currDate = NewTable[Date]

RETURN

CALCULATE( MAX(SourceData[Date]) ,FILTER(SourceData, SourceData[Date] <= currDate) )

and another column

MonthlyVolume = CALCULATE( sum(SourceData[Volume]) ,FILTER(SourceData, SourceData[Date] = NewTable[CorrectDateToCheckVolume]) )

If volume is missing on the next month, it should be replicated from the last month where it was used with the same volume size. For example if we take the row 2.11.2023 ,11, v3, the value of "11" should be applied to December 2023, January 2024, February 2024 and March 2024 for name v3

1 ACCEPTED SOLUTION
Community Support

Hi @w8 ,
Based on the data you provided and my understanding of your description.
You need to make sure that Sourcedata[date] has a relationship with Newtable[date]

First create a calculated column on the Source data to determine if the following months are missing.

``````CorrectDateToCheckName =
VAR currName =
CALCULATE(
MIN(SourceData[Name]),
FILTER(
SourceData,
SourceData[Date] = MIN(SourceData[Date])
)
)
RETURN
IF(
SourceData[Name] <> currName,
currName,
SourceData[Name]
)``````

Secondly, create a column on your Newtable to return the final matching volumn.

``````Volumn =
CALCULATE(
MIN(SourceData[Size]),
FILTER(
SourceData,
SourceData[CorrectDateToCheckName] = SourceData[Name]
)
)``````

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 REPLIES 2
Super User

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @w8 ,
Based on the data you provided and my understanding of your description.
You need to make sure that Sourcedata[date] has a relationship with Newtable[date]

First create a calculated column on the Source data to determine if the following months are missing.

``````CorrectDateToCheckName =
VAR currName =
CALCULATE(
MIN(SourceData[Name]),
FILTER(
SourceData,
SourceData[Date] = MIN(SourceData[Date])
)
)
RETURN
IF(
SourceData[Name] <> currName,
currName,
SourceData[Name]
)``````

Secondly, create a column on your Newtable to return the final matching volumn.

``````Volumn =
CALCULATE(
MIN(SourceData[Size]),
FILTER(
SourceData,
SourceData[CorrectDateToCheckName] = SourceData[Name]
)
)``````

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors