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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ricardocamargos
Continued Contributor
Continued Contributor

Replicate last values

 

Hi there,

 

Does anyone know how to replicate the last value for a non-existing rows ?

For example:

 

I have values for the day 1/1/2018 / 1/2/2018 / 1/4/2018...

In this case I don't have value for 1/3/2018, how can I replicate the last value (1/2/2018) if I don't have this day in my table ?

 

*I'm using a date dimension in this visual. 

 

Thank you,

 

Ricardo

1 ACCEPTED SOLUTION
5 REPLIES 5
Greg_Deckler
Super User
Super User

Typically you do that in Power Query with something like "Fill".


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Hi @Greg_Deckler,

 

Thank for your answer,

 

However the dataset doesn't have this row. just my date table.

 

I should use this function "Fill" when the value is blank, right ?

 

Thank you,

 

Ricardo

Hi @ricardocamargos

 

One approach might be to create a Calculated Table with Missing Dates and Values

 

From the Modelling Tab>>>NEW TABLE

 

New Table =
ADDCOLUMNS (
    GENERATESERIES ( MIN ( TableName[Date] ), MAX ( TableName[Date] ) ),
    "Values",
    VAR Maxdate =
        CALCULATE (
            MAX ( TableName[Date] ),
            FILTER ( TableName, TableName[Date] <= EARLIER ( [Value] ) )
        )
    RETURN
        CALCULATE ( SUM ( TableName[Value] ), TableName[Date] = Maxdate )
)

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

 

It works.

 

Thank you,

 

Ricardo

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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