Forum Discussion
Fill Dates with previous Values
Hi,
I have this table:
| Date | Value |
1 Jan | 2 |
| 2 Jan | 3 |
| 4 Jan | 2 |
| 5 Jan | 4 |
| 7 Jan | 1 |
And I want to show this in a Line Chart, where the empty Dates (3 Jan, 6 Jan) have the same values as the previous dates with existing data, that is:
| Date | Value |
1 Jan | 2 |
| 2 Jan | 3 |
| 3 Jan | 3 (Same value as 2 Jan) |
| 4 Jan | 2 |
| 5 Jan | 4 |
| 6 Jan | 4 (Same value as 5 Jan |
| 7 Jan | 1 |
Which is the best way to do this?
Best regards,
You can try like this, if you have date calendar
if(isblank(SUM(Sales[Sales Amount])), CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day)) SUM(Sales[Sales Amount]))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
4 Replies
You can try like this, if you have date calendar
if(isblank(SUM(Sales[Sales Amount])), CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day)) SUM(Sales[Sales Amount]))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on LinkedinHi Anonymous ,
I just test the measure formula which provided by amitchandak , It works well. First, please make sure you already have one calendar table and create the relationship between calendar table and your fact table using date field. Then create the related measure according to the formula provided by amitchandak , finally drag the field Date in the calendar table and the field Value in the fact table onto the table visual just like below screenshot.
Best Regards
Rena