division should be (by 6) adjusted accordingly

[HKteck]

Hi,

Report link 

https://www.dropbox.com/scl/fi/14g838ufc6d6y9eankp3e/Data-2024-PBI.pbix?rlkey=9hpijshbqnaavroceyqnbr... 

In the daily view, data for the 6th is missing.

In the weekly view, the division by 7 is used to calculate averages, but when there is missing data for specific days, like on the 7th and 14th of January 2024, the division should be (by 6) adjusted accordingly. Instead of dividing by 7, it should only divide by the number of available days, which in these cases would be 6, since data is not available for the 6th and 13th of January 2024.

Kindly help how I can perform this calculation 

@MDax @dax 

[v-yohua-msft · Accepted solution]

Hi, @HKteck 
Based on the information you gave, I have created a table

Please follow these steps:

1. Create a new measure and named  Available Days. We first count the number of available days, then use slicers or filters to filter the number of available days from Monday to Sunday, and finally calculate.

2. Then use the following DAX expression.

Available Days = CALCULATE(

         DISTINCTCOUNT('Data 2024'[Date]),

         FILTER(ALLSELECTED('Data 2024'),'Data 2024'[Date]<=MAX('Data 2024'[Date])

         ))

Quantity Weekly = 'Data 2024'[Quantity]/'Data 2024'[Available Days]

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

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

[amitchandak]

@HKteck , Assume you already have a measure M1 and using the date table and date and week coming on axis from the date table 

You can use measure like

Averagex(Values(Date[Date]), [M1])

In this it will take only 6 days avg it data is available for 6 days of week