cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LarsTCCon Regular Visitor
Regular Visitor

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.

 

    
  MonthMeasure
What I haveCustomer 1Feb/2010
 Customer 1Apr/2020
    
What I wantCustomer 1Jul/19 
 Customer 1Aug/19 
 Customer 1Sep/19 
 Customer 1Oct/19 
 Customer 1Nov/19 
 Customer 1Dec/19 
 Customer 1Jan/20 
 Customer 1Feb/2010
 Customer 1Mar/20 
 Customer 1Apr/2020
 Customer 1May/20 
 Customer 1Jun/20 

    

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AnkitBI Established Member
Established Member

Re: Show all months even if no values

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]))

View solution in original post

4 REPLIES 4
AnkitBI Established Member
Established Member

Re: Show all months even if no values

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.

LarsTCCon Regular Visitor
Regular Visitor

Re: Show all months even if no values

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 Established Member
Established Member

Re: Show all months even if no values

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]))

View solution in original post

LarsTCCon Regular Visitor
Regular Visitor

Re: Show all months even if no values

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

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,516)