Create a Matrix with Filtered Column Items based on a dynamic date range.

[charlie_torres]

Dear @community,
On the need for help again :[ after reading about many similar situations in the forum, but not quite the intended solution. The current problem is creating a matrix that follows a weekly dynamic date change. The matrix needs to filter the column items (properties) whose date for the respective Milestone achieved lies on the range of dates for the current week.

Please see below mock image of the Milestones table:

Also an example of the matrix I intend to produce:

I got the dynamic dates that will change based on the date of access to the report, to ensure always showing current week records:

Week Commencing =
VAR weekComenc =
TODAY() - WEEKDAY( TODAY(), 3)
RETURN
weekComenc

Week Ending =
VAR weekEnd =
TODAY() - WEEKDAY( TODAY(), 3) + 4
RETURN
weekEnd

Plus, the DAX I thought would be the solution if I create this measure for each milestones to later adding them all into the matrix:

EVALUATE( ADDCOLUMNS ( VALUES ( 'Milestones table'[Property] ),
"Current Milestone Date", CALCULATE ( COUNTROWS ( 'Milestones table'), FILTER( 'Milestones table', 'Milestones table'[Pre-Survey Meeting] > 'Milestones table'[Week Comencing] ), FILTER( 'Milestones table', 'Milestones table'[Pre-Survey Meeting] <= 'Milestones table'[Week Ending] )))

After trying this one I got error: Multiple columns cannot be converted to a scalar value.

As usuall all help, answer or links for a solution are highly appreciated

Charlie

[charlie_torres]

Hi @v-shex-msft,

Your suggestion is exactly what I was looking for. It's starting to look accordingly. However, The only obstacle I am finding now is filtering what the matrix displays based on the dynamic range of dates I created. Therefore, this is only shown if a date associated with a building is within range.

Any idea how I can accomplish this?

Many Thanks
Charlie

[v-shex-msft · Accepted solution]

Hi @charlie_torres,

I'd like to suggest you use measure formula to instead the value field to use on matrix.

You can use this formula to lookup raw table records and add a variable to extract the current row context date to use in compare with system date steps.

After these steps, you can use an if statement to check current row context data to prevent the calculation in the not match ranges and move the corresponding expression into the if statements true branch.
Regards,

Xiaoxin Sheng

[charlie_torres]

Hi @v-shex-msft 

That was it. It is fully working as expected.

Thank you very much

Regards
Charlie

[v-shex-msft]

Hi @charlie_torres,

I'd like to suggest you use unpivot columns feautre on the milestones fields to convert them to attribute and value.

Then you can use the unpivoted attribute on the matrix row, property field on column, the raw table date field on value fields with aggregate(first or last).

Unpivot columns - Power Query | Microsoft Learn

Regards,

Xiaoxin Sheng