Forum Discussion
Create Rows to the table where the data is missing for specific dates
Hello All,
I have a table with below columns in my dataset. We have monthly revenue for each resellers in this table from feb-21 to mar-22.
For few resellers, there will be no data for some particular months, as they didn’t generate revenue on those months. I want to create rows for those resellers with the missing date and the revenue for those missing dates to be updated as blank.
Please advise how we can achieve this.
Current data:
Expected result:
6 Replies
Hello Anonymous,
Can you please try this -
1. Create one CalculatedTable using DimCalendar = CalenderAuto() function.2. Give relationship of DimCalendar and you Data Table on Date Column.
3. Pull Date field from DimCalendar in your visual instead of your Data Table.
4. Enable "Show Items with No Data" for Date column in field option for your visual.
Please mark it as solution if it resolves your issue. Kudos are also appreciated.
Cheers,
Shishir
Hi Shishir22
I actually want to create rows in the table where the data is missing for particular month. it should show all the dates(feb-21 to mar-22) for all the customers with the revenue as blank whereever the date is missing for the customers
Can you please try above steps.
It should show expected result.
Thanks,
Shishir
Hi,
Please try to replace your [Revenue] measure with the below.
Revenue measure fix: = COALESCE ( [Revenue], "" )Jihwan_Kim there is no change in the table if i add this measure
