Forum Discussion

Jitmondo's avatar
Jitmondo
Helper III
4 years ago
Solved

Matrix- Month and Date missing when no data!

Hi All,

 

I have a matrix table where there are values in each month and year (Cumulative). Made a fake example in excel below.

When I filter to a specific team that has no data in the month then the column dissapears. I want to display the whole 12 months regardless if we have no values in them. The option to display values with no data is missing too :S

 

I am using this formula for the values in the matrix:

Cum_Weighted_Pipeline = CALCULATE(SUMX('Opportunity','Opportunity'[Weighted Pipeline]), FILTER(ALL('Dates'),'Dates'[Date]<=MAX('Dates'[Date]) && (YEAR('Dates'[Date])>=YEAR(TODAY()))))
 
Any ideas on what I could do or if it is actually possible to get the desired outcome I want ?
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Jitmondo , In this case, if you are using month year from date table, You should get all the month

     

    or add +0

     

    Cum_Weighted_Pipeline = CALCULATE(SUMX('Opportunity','Opportunity'[Weighted Pipeline]), FILTER(ALL('Dates'),'Dates'[Date]<=MAX('Dates'[Date]) && (YEAR('Dates'[Date])>=YEAR(TODAY())))) +0

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Jitmondo , In this case, if you are using month year from date table, You should get all the month

     

    or add +0

     

    Cum_Weighted_Pipeline = CALCULATE(SUMX('Opportunity','Opportunity'[Weighted Pipeline]), FILTER(ALL('Dates'),'Dates'[Date]<=MAX('Dates'[Date]) && (YEAR('Dates'[Date])>=YEAR(TODAY())))) +0