matrix visual column range

[jmeyer348]

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-month values shown at the top as the column headers to only span the last 12 completed calendar months.  So in this example, the range would be from 2023-10 to 2024-09.  I know that I can set a visual level filter - under relative date filters, I can select last 12 calendar months.  But if I do this, it will filter out rows that don't have any data in the last 12 months (such as the highlighted materials in the image below, these would be filtered out because they don't have any usage between 2023-10 and 2024-09).   What I want instead is to show all material numbers, and if they don't have any usage in the last 12 completed months, it should just show blank in all 12 cells in that row.  Does anyone have a good way to do this?

[jmeyer348]

@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 · Accepted solution]

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

@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

[Ashish_Mathur]

Hi,

I think there is a setting to show items with blanks.  Try to turn that on.  Not even sure whether that setting applies to a matrix table or not - please check.

[jmeyer348]

@Anish_Mathur, Can you tell me where  can find this option?  I do see a "blank rows" toggle under the visualization pane, but all this appears to do is insert a blank row in between each populated row.  It doesn't keep material numbers that have no usage in the last 12 months.

[Ashish_Mathur]

You already have a solution.

[NordicDrow]

The first idea that comes to mind is just creating a duplicated table, just keep the material and Main Customer column (for ease of use), relate by Customer, Material or generate a new column in each table that would basically be  a concat of this columns (single direction relationship, otherwise the new table will also be filtered), use the new table material and customer columns in rows.

Its not ideal, but if I understand your needs it should work, matrixes are a bit hard to work with, they are quite limited. The best way otherwise would be using a measure ALL(Material) but it does not work like this.