Creating average on month with blanks

[wolfjnh]

Hi,

I have a small problem, which lifts me to my edge.

I have two tables:

Dates - typical date dimension with one row per day

SOC Tickets - tickets from our bug tracking tool with different categories etc.

Connected on Datum and Erstellt - Datum

Now I just want to create the average amount of tickets per month.

I started with:

= COUNT('SOC Tickets'[key]) / DISTINCTCOUNT('SOC Tickets'[Monat mit Jahr]);

Monat mit Jahr is Yearmonth and is just a related DAX: 

Monat mit Jahr = RELATED(Dates[Monat mit Jahr])

So far, so easy. I thougt, this would work, but when I filter on the data, I had to find out, that months without data for this category are ignored in the calculation. I added a simple example for you.

My expecatiation is, that PBI takes the month from 01.06.2017 to 31.10.2018 (17) and does following calculation

2 / 17 = 0,1176

but it ignores all months without a value and so I get a

2 / 2 = 1

How can I achive, that PBI gives me the right value? I found similiar posts, but nothing worked for me. 

[AlB · Accepted solution]

Hi @wolfjnh

Try

DISTINCTCOUNT('Date'[Monat mit Jahr])

in your formula

'SOC tickets'[Monat mit Jahr] only has the months in which there is data.  The 'Date' table has all of them.

[AlB · Accepted solution]

Hi @wolfjnh

Try

DISTINCTCOUNT('Date'[Monat mit Jahr])

in your formula

'SOC tickets'[Monat mit Jahr] only has the months in which there is data.  The 'Date' table has all of them.

[wolfjnh · Accepted solution]

Hi @AlB,

thank you for your post. I think, I found the solution.

First of all, you are right. I have to use the Date-Dimension in this formula.

But I have also to consider two more things. The connection between the two tables has to be unidirectional and the date filter in my report needs to come from my date table as well.

I will look, if there are side effects to my other reports, but I think, that this is the solution. Simple, just as I thought 🙂

Regards,

wolfjnh

[AlB]

@wolfjnh

Correct on both.  I had not seen you had a bidirectional relationship

Best

[Stachu]

can you share an anonymised sample of your data?
you can follow the advice here
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490