Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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% |
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |