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
Anonymous
Not applicable

Fill Dates with previous Values

Hi,

I have this table:

DateValue

1 Jan

2

2 Jan3
4 Jan2
5 Jan4
7 Jan1

 

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:

DateValue

1 Jan

2

2 Jan3
3 Jan3 (Same value as 2 Jan)
4 Jan2
5 Jan4
6 Jan4 (Same value as 5 Jan
7 Jan

1

 

Which is the best way to do this? 

 

Best regards,

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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

Anonymous
Not applicable

Thanks,

I tried your measure but seems that there are still blank values 

date.png

 

Calendar table:

calendar.png

 

Measure:

measure.png

Hi @Anonymous ,

If your problem has been resolved? If still not, could you please some sample data in table BurnUpPointsData? Need to check if the variance "Actual" in your measure include any blank value. Thank you.

 

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @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.

Fill Dates with previous Values.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.