DAX: Drill Up / Down averages with missing data in time series

[eyz]

I have a dataset that looks like the following:

Date	Category	Value
1/31/2018	A	1
2/28/2018	A	1
3/31/2018	A	1
4/30/2018	A	1
5/31/2018	A	1
6/30/2018	A	1
7/31/2018	A	1
8/31/2018	A	1
9/30/2018	A	1
10/31/2018	A	1
11/30/2018	A	1
12/31/2018	A	1
1/31/2019	A	1
2/28/2019	A	1
3/31/2019	A	1
4/30/2019	A	1
5/31/2019	A	1
6/30/2019	A	1
7/31/2019	A	1
8/31/2019	A	1
9/30/2019	A	1
10/31/2019	A	1
11/30/2019	A	1
12/31/2019	A	1
1/31/2018	B	1
2/28/2018	B	1
3/31/2018	B	1
4/30/2018	B	1
5/31/2018	B	1
6/30/2018	B	1
7/31/2018	B	1
8/31/2018	B	1
9/30/2018	B	1
10/31/2018	B	1
11/30/2018	B	1
12/31/2018	B	1
1/31/2019	B	1

As you can see, Category A spans the full 2 years while Category B only shows up once in 2019 (in January). When I plot both series and use the "Drill Up" function, we see the following:

In 2019 Q1, we see that the average for B = 1. But I would like the average for B in 2019 Q1 to be 1/3, like the following:

(Since B only shows up in January 2019).

(Formula for the measures that I am plotting: value = average('data'[A]) )

I was able to get the 2nd plot by manually adding B = 0 entries for Feb 2019 through Dec 2019, but was wondering: does anyone know of a DAX solution that doesn't require manual intervention? (Perhaps by way of inserting 0s by itself...) Any help is much appreciated, thanks!

[lbendlin · Accepted solution]

To report on things that are not there you need to use disconnected tables and/or crossjoins. So in your case you would need a disconnected calendar table.

[Anonymous · Accepted solution]

Hi @eyz ,

@lbendlin  provided a good idea, and I have another method here, I hope it will be helpful to you.

1. In the power query editor, select the category column and pivot it.

2. Select replace values and replace null with 0.

3. The final result is shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[lbendlin · Accepted solution]

To report on things that are not there you need to use disconnected tables and/or crossjoins. So in your case you would need a disconnected calendar table.