cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Show all months even if no values

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

1 ACCEPTED SOLUTION
Solution Sage

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]))`
4 REPLIES 4
Solution Sage
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
Not applicable

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.

Solution Sage

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
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.