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

[Anonymous]

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

[Ashish_Mathur]

Hi,

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

[v-heq-msft · Accepted solution]

Hi @Anonymous ,
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