How to show dates with no data and assign them a percent value

[Anonymous]

I have a Power BI report that calculates attainment percentages (# completed / # total) on a weekly and YTD basis based on daily data. 

To first calculate the daily attainment, I have created measures 

On-Time (# of Ys or # completed for that day): calculate(countrows(query1),query1[OPCOMP] = "Y"

Total (# of Ys and Ns or total # work orders for that day): calculate(countrows(query1),query1[WRKNO])

Daily attainment (# on time / total): divide([On-Time],[Total])

I am applying a work center (WC) filter to this dataset to only capture the attainment for some groups. When doing this, I lose some of the weekdates, and there are gaps in the line graph. This is because the groups I am analyzing may not have had a work order to complete for that day, and there is no data for that group even though there is data for other groups. 

The desired result is to fill in those missing week days and display the attainment as 100% (0 on time vs 0 total). Currently, the visuals do not show these dates with any sort of value at all.

Below is the source data in which I later filter for particular work centers. 

Below is the line graph I am trying to fill in with all dates (missing ones to have attainment percentage of 100%)

Any help is greatly appreciated. 

[v-yanjiang-msft]

Hi @Anonymous ,

Has @TomMartens 's solution solved your problem? If yes, please consider accept it as the solution to help the other members find it more quickly.

If not, please feel free to let me know.

Best Regards,
Community Support Team _ kalyj

[TomMartens]

Hey @Anonymous ,

showing dates w/o data requires a dedicated calendar table, this article contains almost everything one need to know about date-based/time-based calculations using DAX: Time patterns – DAX Patterns.

The calendar table has to be related to your existing table. The Calendar table is on the one-side of the relationship, your table on the many-side.

Then you have to adapt your measures to utilize the column used on the line chart and return 100 if there is no value otherwise return the value of your measure.

Use the appropriate column on the axis of the line chart

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom