topic Re: Use DAX to show dates without data with previous available record in DAX Commands and Tips

Use DAX to show dates without data with previous available record

Nomadexchris — Wed, 09 Jun 2021 08:00:10 GMT

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!

Re: Use DAX to show dates without data with previous available record

AlB — Wed, 09 Jun 2021 08:09:35 GMT

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.

Re: Use DAX to show dates without data with previous available record

Jihwan_Kim — Wed, 09 Jun 2021 08:25:11 GMT

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

Re: Use DAX to show dates without data with previous available record

Nomadexchris — Wed, 09 Jun 2021 08:25:28 GMT

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

Re: Use DAX to show dates without data with previous available record

Nomadexchris — Wed, 09 Jun 2021 09:03:05 GMT

Dear Jihwan,

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