Date Dimension Table and Calculating Averages

[pstp]

I have a fact table with event attendance that has multiple data points for the same day/week (for different locations and times). I also have a master date dimension table. The two tables are related. In 2020, we had multiple weeks where we did not host events, and therefore have no data for those dates in the fact table. I'm trying to calculate average weekly attendance. When I use the formula

Average = SUM(Fact_Table[YValue]) / DISTINCTCOUNT(Dim_Date_Table[CalendarWeekNumber])

the distinct count returns 53, not the number of weeks that we have data for in the fact table. Is there a way to use the date table in the calculation, but for it to only use data for corresponding dates in the fact table? Thanks!

[nvprasad · Accepted solution]

Hi 

Can you try with belwo dax?

Average = SUM(Fact_Table[YValue]) / CALCULATE(DISTINCTCOUNT(Dim_Date_Table[CalendarWeekNumber],FACTTABE[DATECOLUMN]))

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad