Use DAX to show dates without data with previous available record

[Nomadexchris]

Hi guys, recently I was getting stuck on an issue that I would like to solve with DAX.

Let's say I have a sample sales data with two products each showing their respective price whenever there is a price change

As you could see, not all the dates between the earliest and latest date are included,

and I would like to show a DAX that basically shows all the data in between.

let's taking product 2 as an example, the earliest available date would be 1/5/2021 and there is no price change on 2/5/2021.

I would like to show in DAX/measure that on 2/5/2021, the price for product 2 is still 1,490. Same for all the date without data.

I know this problem is easy to solve when the data size is small but it's hard to do it with millions of rows.

Any help is appreciated. Thanks!

[Jihwan_Kim · Accepted solution]

Hi, @Nomadexchris 

Please check the below picture and the sample pbix file's link down below.

Price Update Measure =
IF (
ISFILTERED ( Products[Product] ) && ISFILTERED ( Dates[Date] ),
CALCULATE (
LASTNONBLANKVALUE ( 'Price'[Date], MAX ( 'Price'[Price] ) ),
FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)
)

https://www.dropbox.com/s/yxrfqjs709fbcf4/nomadex.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Nomadexchris]

Dear Jihwan, 

It works like a charm. Thanks very much for the quick response. Never thought the answer is quick simple!!!!

[AlB]

Hi @Nomadexchris 

What is the desired output for this? A table? Do you need it in PQ or in DAX?

Share a smaple of the input data in text-tabular format so that the contents can be copied

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Nomadexchris]

Hi AIB, thanks for the reply. The desired output would be DAX which shows all the empty dates with records from the previous date. Here is the input table.

Date Product Price

1/5/2021 Product_01 1990

2/5/2021 Product_01 1890

3/5/2021 Product_01 1790

1/5/2021 Product_02 1490

3/5/2021 Product_02 1290

7/6/2021 Product_02 1190

9/6/2021 Product_02 1290