Repeat data for the missing date

[Surekha_PM]

Hello Everyone,

I am creating a word cloud for text data in my dashboard. I am receiving the data on random days. There is no exact gap in the date when I am receiving the data.

For example, the first data I received was on 31st January, 2025, and the next is 15th February, 2025, and then 31st March, 2025, etc. Now I want to repeat the data for the missing date and show the last available date's data in the word cloud. The filter in the word cloud is calendar date. When a user is filtering the date, which has no data, it should show the last available date's data as it is. 

Can it be done in Power BI?

Regards,
Surekha

[Surekha_PM · Accepted solution]

Thank you @jaineshp & @Rupak_bi,

I actually took a different approach to solve the issue. I created a new table with a calendar date till today. And then did a lookup and pasted the data in that new table. Now in the new table I am able to see the data for every day. This is showing me correct data, so I am continuing with this. But I really appreciate your response. Thank you for the suggestion.

Regards,
Surekha

[v-mdharahman]

Hi @Surekha_PM,

I am glad that your issue has been resolved. As you already provided the correct work around which helped you in solving your issue, kindly mark it as solution so that other community members facing the same issue can find the solution quickly.

Best Regards,

Hammad.

[jaineshp]

Hey @Surekha_PM,

Thank you for your feedback.

Glad to know you're back on track!

Best Regards,
Jainesh Poojara | Power BI Developer

[Rupak_bi]

Hi @Surekha_PM ,

Considering in the original dataset you have columns , Date and Data.
1. Now create a calculated table with all dates

table = calender(date(2025,1,1),date(2025,12,31))

2. Create a new column as below

latest data = 
var last_date = calculate(max(main_table,date),all(main_table),main_table,Date<=new_tabe,Date)
result
calculate(max(main_table,Data),main_table,Date = last_date)

Hope this will work. if not, please share sample data to better understand the pattern and model.

[jaineshp]

Hi @Surekha_PM ,

Yes, this can be accomplished in Power BI using a combination of calendar table and DAX measures.

Step-by-Step Approach:

1. Create a Calendar Table

• Generate a continuous date table covering your full date range
• Include all dates from your earliest data point to current/future dates

2. Establish Relationship

• Link your calendar table to your data table using the date field
• Ensure proper one-to-many relationship is established

3. Build DAX Measure for Last Available Data

Last Available Text =
VAR SelectedDate = MAX('Calendar'[Date])
VAR LastDataDate =
CALCULATE(
MAX('YourDataTable'[Date]),
'YourDataTable'[Date] <= SelectedDate
)
RETURN
CALCULATE(
CONCATENATEX('YourDataTable', 'YourDataTable'[TextColumn], " "),
'YourDataTable'[Date] = LastDataDate
)

4. Configure Word Cloud Visual

• Use the DAX measure as your text source instead of direct column
• Set up date slicer using the calendar table
• The word cloud will now show last available data for any selected date

5. Additional Considerations

• Handle null scenarios with ISBLANK() checks in your DAX
• Consider performance optimization for large datasets
• Test thoroughly with various date selections

This approach ensures users always see relevant word cloud data regardless of the selected date, pulling from the most recent available dataset when no exact match exists.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best regards,
Jainesh Poojara / Power BI Developer