Script to create matrix table automatically

[Anonymous]

Hello, is it possible create a script to generate on matrix table every week automatically?

Per example I need that power bi generate theses table bellow when the data is updated. Every week with some filter the power bi create this report automacally per week. It is to compare in text week per week

2020-01-23
NET Sales (								Gross Sales (in bags)
Territory	Open Orders	Invoiced + Rleased	Sales YTD	GAP YTD	Total Plan	% Completed		Territory	Open Orders	Invoiced + Rleased	Sales YTD	GAP YTD	Total Plan	% Completed
Centro Norte	10	15	25	75	100	25%		Centro Norte	10	15	25	75	100	25%
Oeste	10	15	25	75	100	25%		Oeste	10	15	25	75	100	25%
Sudeste	10	15	25	75	100	25%		Sudeste	10	15	25	75	100	25%
Sul	10	15	25	75	100	25%		Sul	10	15	25	75	100	25%
TOTAL	40	60	100	300	400	25%		TOTAL	40	60	100	300	400	25%
2020-01-30
NET Sales (in bags)							Gross Sales (in bags)
Territory	Open Orders	Invoiced + Rleased	Sales YTD	GAP YTD	Total Plan	% Completed		Territory	Open Orders	Invoiced + Rleased	Sales YTD	GAP YTD	Total Plan	% Completed
Centro Norte	15	20	35	65	100	35%		Centro Norte	15	20	35	65	100	35%
Oeste	15	20	35	65	100	35%		Oeste	15	20	35	65	100	35%
Sudeste	15	20	35	65	100	35%		Sudeste	15	20	35	65	100	35%
Sul	15	20	35	65	100	35%		Sul	15	20	35	65	100	35%
TOTAL	60	80	140	260	400	35%		TOTAL	60	80	140	260	400	35%
2020-02-06
NET Sales (in bags)							Gross Sales (in bags)
Territory	Open Orders	Invoiced + Rleased	Sales YTD	GAP YTD	Total Plan	% Completed		Territory	Open Orders	Invoiced + Rleased	Sales YTD	GAP YTD	Total Plan	% Completed
Centro Norte	10	15	25	75	100	25%		Centro Norte	10	15	25	75	100	25%
Oeste	10	15	25	75	100	25%		Oeste	10	15	25	75	100	25%
Sudeste	10	15	25	75	100	25%		Sudeste	10	15	25	75	100	25%
Sul	10	15	25	75	100	25%		Sul	10	15	25	75	100	25%
TOTAL	40	60	100	300	400	25%		TOTAL	40	60	100	300	400	25%

[sturlaws · Accepted solution]

Hi @Anonymous 

there is no need for scripting or generating new matrices.

In your date/calendar table, create a new column like this:

yearWeekNum = year(dates[Date])*100+WEEKNUM(dates[Date])

Then create a second column like this:

relative week =
VAR _currentWeek =
    CALCULATE ( SELECTEDVALUE ( dates[yearWeekNum] ) )
VAR _todayWeek =
    YEAR ( TODAY () ) * 100
        + WEEKNUM ( TODAY () )
VAR _numberOfWeeks =
    IF (
        _currentWeek <= _todayWeek;
        CALCULATE (
            DISTINCTCOUNT ( dates[yearWeekNum] );
            FILTER (
                ALL ( dates );
                dates[yearWeekNum] >= _currentWeek
                    && dates[yearWeekNum] <= _todayWeek
            )
        ) - 1;
        CALCULATE (
            DISTINCTCOUNT ( dates[yearWeekNum] );
            FILTER (
                ALL ( dates );
                dates[yearWeekNum] <= _currentWeek
                    && dates[yearWeekNum] > _todayWeek
            )
        ) * -1
    )
RETURN
    _numberOfWeeks

Say you have 3 matrices, one for each of the 3 previous weeks. On the first one, add the column Dates[Relative week] to the filter section of the visual, and set it to filter for [Relative week]=1. For the next to matrices, set it to 2 and 3 respectively.

The code above sets the current week to 0, weeks in the past are positive numbers, weeks in the future is negative numbers.

Every time this model is updated, this column will be evaluated with respect to the current date, by the TODAY-function, so it will always filter the visuals according to the date when the model was last updated. 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.