Try to show the missing rows from a table in my report.

[charlieguerry]

Hello everyone,

I receive sales files every month from different sources ; I merge them in PBI to show KPIs to my team.

Some months, I don't receive a file from one or some source, so the "YearToDate" KPIs do not represent the reality because a file is missing for a month.

For the moment, I show, in a table next to my KPIs visualizations, all the "avaibalble" months for each source.

The problem is that the list can be long if we multiply the sources and the months.

Below an example of 3 sources for YTD from January to April 2019.

We can see that I miss March & April for BRAZIL FLEET PORTAL and only April for FLOTTE AMICHE.

- The perfect solution would be for the table shows only the missing months. But I just don't know how to do this.

- An other solution would be showing for each source and for each year, the number of month available.

I tried by creating a measure with this function : NbMonths = DISTINCTCOUNT(DATES[DATE].[Month])

But I finally have this result :

 The "4" probably corresponds to the number of months I choose in my filter to do my YTD Visualization.

If you have an idea about it, please tell me ! 🙂

Thanks!

[Stachu · Accepted solution]

does this work properly? you need to adjust the Table name

NoOfMonths = 
COUNTROWS(SUMMARIZE(Table, Table[SOURCE], DATES[MONTH]))

[charlieguerry]

Hello Statchu, Thanks it's working !

I can be staisfied with this solution.

But the perfect solution would be to just show the missing months ; if you understand.

Do you think it's possible ?

Thank for your help !

[Stachu]

 try something like this

Measure = 
VAR __MonthsInScope = SUMMARIZE('Table',DATES[Month])
VAR __AllMonths = ALLSELECTED(DATES[Month])
VAR __MonthsNotInScope = EXCEPT(__AllMonths, __MonthsInScope)
RETURN
CONCATENATEX(__MonthsNotInScope, [Month], ";")

[charlieguerry]

It doesn't work, but I get the idea so I will work on it. 😉

I will post the "algorithme" if I can get it to work.

Thank you a lot Stachu ! 🙂