Forum Discussion

jmeyer348's avatar
jmeyer348
Frequent Visitor
1 year ago
Solved

matrix visual column range

Hi everyone!         I'm having a bit of an issue with a matrix visual that I'm trying to create.  I've reproduced a simpliefied version of the issue in the image below.  Basically, I want the year-...
  • jmeyer348's avatar
    jmeyer348
    1 year ago

    Kedar_Pande , Solved! I used your method, except I made one change.  I updated the "Usage" measure to return 0 if it did not find any rows to do the calculation.  Here is the DAX code:  

    Usage =
    VAR Last12Months =
    CALCULATE(
        SUM(Sheet1[Total Usage]),
        FILTER(
            DateTable,
            DateTable[Date] >= EDATE(TODAY(), -12) && DateTable[Date] < TODAY()
        )
    )
    RETURN
    IF(ISBLANK(Last12Months), 0, Last12Months)
     
    Once I made this change, I was able to see rows that did not have any usage in the last 12 months.

     

jmeyer348's avatar
jmeyer348
Frequent Visitor
1 year ago

Kedar_Pande Tried this, and it removed the materials that did not have any usage in the last 12 months.  This is the same problem I've had with every method I've tried so far.  As you can see, in the last picture, the material numbers that I had originally highlighted are gone.

 

 

jmeyer348's avatar
jmeyer348
Frequent Visitor
1 year ago

Kedar_Pande , Solved! I used your method, except I made one change.  I updated the "Usage" measure to return 0 if it did not find any rows to do the calculation.  Here is the DAX code:  

Usage =
VAR Last12Months =
CALCULATE(
    SUM(Sheet1[Total Usage]),
    FILTER(
        DateTable,
        DateTable[Date] >= EDATE(TODAY(), -12) && DateTable[Date] < TODAY()
    )
)
RETURN
IF(ISBLANK(Last12Months), 0, Last12Months)
 
Once I made this change, I was able to see rows that did not have any usage in the last 12 months.