I am computing the average monthly transactions that come through patrons. The dates are filtered using parameters as shown below. What we need is that the calculation should take all months selected in the period, so that would be 12. But using DistinctCountOver() I am getting 10 months because there was no data for this partner in 2 of the months this year. Is it possible to bypass the partner filter in the calculation and get 12 months as the distinct count?
This is not a Quick Sight issue. Your dataset should have the date values for those two months, with the patron being null and then the count would return 12.
In Quick Sight you could handle this by having a separate date table and do a left join with your fact table on the date column. Use the date table for getting the distint count. Doing this way the size of your dataset will grow as it will create NULL rows for cases where there is no data. You need to be aware of that.
