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

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

Reply
w8
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
v-heq-msft
Community Support
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]

vheqmsft_1-1711591519651.png

 


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

vheqmsft_0-1711591420732.png

 

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

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
v-heq-msft
Community Support
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]

vheqmsft_1-1711591519651.png

 


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

vheqmsft_0-1711591420732.png

 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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