Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to Solution.
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
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
Thanks,
I tried your measure but seems that there are still blank values
Calendar table:
Measure:
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
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.
Best Regards
Rena
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |