Date table DAX Help

[Anonymous]

Hi All,

I have a an attendance table which is tied to a date table. Issue I am having is there isnt data for every single day. I want the 0 days to count e.g.

Mon - 0%

Tue - 0%

Wed - 7%

Thu - 0%

Fri - 0%

The calculation I am trying to do would be 0+0+7+0+0 = 7 divided by 5 = 1 (rounded up)

How do I edit this to include 0 days? The highlighted is the only day in that week that had attendance

AVG % (M-F) = VAR

                WeekdayTable= FILTER(dDates, dDates[DayOfWeek] <> 6 && dDates[DayOfWeek] <> 0 && dDates[Year] = 2022)

            RETURN

            AVERAGEX(WeekdayTable, [DailyAttendance])

[Mikelytics]

Hi  @Anonymous 

Please try putting "+0" behind your formula

AVG % (M-F) = 
VAR WeekdayTable = 
   FILTER(dDates, dDates[DayOfWeek] <> 6 && dDates[DayOfWeek] <> 0 && dDates[Year] = 2022)
RETURN
AVERAGEX(WeekdayTable, [DailyAttendance]) + 0

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[Anonymous]

Hey,

That adds 0 at the Year level not the day level

[Mikelytics]

ok, I have overseen something. Can you please try the following:

AVG % (M-F) = 

VAR WeekdayTable = 
   FILTER(dDates, dDates[DayOfWeek] <> 6 && dDates[DayOfWeek] <> 0 && dDates[Year] = 2022)
RETURN
AVERAGEX(WeekdayTable, [DailyAttendance] + 0) 

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[Anonymous]

Hey, this seems much closer than what I initially had but for the below calendar week where the highlighted falls, the answer should be 1.4% because Mon, Wed, Thu, and Friday are 0% but Tuesday is 6.6%. Im struggling to see what Im missing 😞

[Mikelytics]

Hi  @Anonymous 

what really confues me is that you one the one hand speak about weeks but on the other hand you put single dates into the visual and in a previous picture you put year values into a bar chart.

Can you please show the data model and explain the meening of the columns in your table visual?

Best regards

Michael

[Anonymous]

I created a date table that has all the different dimensions

[Mikelytics]

Can you please sho the definition of the measures you use (e.g. daily attendance or headcount)?

[Anonymous]

Because I tied my date table to the fBrazilBadge table, the tqble visual only shows dates that have data. I need to be able to show all dates even ones that have no data, that way they are included in the AverageX DAX

[Anonymous]

[Mikelytics]

Hi @Anonymous 

To be honest its really hard to provide a solution in this case. I assume that you have some challenges with the granularity and relations between the tables. Can you maybe provide an PBIX example with dummy data?

Best regards

Michael