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.

     

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

jmeyer348 

Ensure you have a Date table with a Year-Month column.

Relate this Date table to your data table on the date field.

Measure for Usage:

Usage = 
VAR Last12Months = 
CALCULATE(
SUM(YourDataTable[UsageColumn]),
FILTER(
YourDateTable,
YourDateTable[Date] >= EDATE(TODAY(), -12) && YourDateTable[Date] < TODAY()
)
)
RETURN
IF(ISBLANK(Last12Months), BLANK(), Last12Months)

Use material numbers in the Rows.
Use the Year-Month column from the Date table in the Columns.
Use the Usage measure for values.

 

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn