Show all months even if no values

[Anonymous]

Hi 

I need to show a matrix table with 12 months for each customer. In rows I have MMM-YY from my Calendar table and as values I have different measures representing realised revenue, cost and so on. 

The problem: If there is no values for for my measures for a given customer-month combination I get no row. What I want to accomplish is a blank row instead. I am not interested in showing customers, when there is no values only MMM-YY. I am open for any suggestion, but I prefer to do it in DAX and I don't want to manipulate my input table with new rows.

		Month	Measure
What I have	Customer 1	Feb/20	10
	Customer 1	Apr/20	20
What I want	Customer 1	Jul/19
	Customer 1	Aug/19
	Customer 1	Sep/19
	Customer 1	Oct/19
	Customer 1	Nov/19
	Customer 1	Dec/19
	Customer 1	Jan/20
	Customer 1	Feb/20	10
	Customer 1	Mar/20
	Customer 1	Apr/20	20
	Customer 1	May/20
	Customer 1	Jun/20

[AnkitBI]

Have you tried Show "Items with No Data" for Customer field. This should work based on your issue description.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[Anonymous]

Hi Ankit

Thanks, but It is not really helping since it will show me all months in the Calendar table. I only want the 12 months and ALL 12 months, as in the example. 

[AnkitBI · Accepted solution]

Can create a measure like this. You may need to modify as per requirement. Below will return -1 if month is less than 12 the current month else value. Then filter records with -1 in your Visual filter pane for matrix and show row with no data for blanks.

 Let me know if this resolves it or share your pbix file.

Measure 4 = if(DATEDIFF(max(Data[Date]),now(),MONTH) > 12,-1,sum(Data[Salary1]))

[Anonymous]

Thanks.. I need to do some more tweaks, but I can work with this idea. Thanks!