Forum Discussion
Use DAX to show dates without data with previous available record
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!
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
4 Replies
- Jihwan_Kim
Super User
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
Dear Jihwan,
It works like a charm. Thanks very much for the quick response. Never thought the answer is quick simple!!!!
- AlB
Community Champion
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.
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
