Hi fellow Power BI enthousiasts,
Introduction
I'm working a power BI dashboard that monitors the performance of teams on different scores for every week for an organisation I'm working for.
Data
At unregelar moments I receive new data for a specfic week for all teams. This can be once a month, but also twice. The below table displays the data I'm receiving:
| Account | Project | Comment | Weeknumber | GeneralScore* |
| A | Project A | Good performance | 2 | 3 |
| A | Project B | Working hard | 2 | 1 |
| B | Project C | Getting better | 1 | 8 |
| B | Project D | Not yet on full capacity | 1 | 6 |
* GeneralScore is a score of team performance that is 1, 2, or 3.
I want to display this data in the following table in PBI:
| Account | Project | Comment | Weeknumber | GeneralScoreT-2** |
| A | Project A | Good performance | 2 | 3 |
| A | Project B | Working hard | 2 | 1 |
| B | Project C | Getting better | 1 | *** |
| B | Project D | Not yet on full capacity | 1 | *** |
Issue
** Is the GeneralScore of a team on CURRENTWEEK-2. So currently we are in week 4, which will means that the column is displaying the GeneralScores for all teams on week 2.
***If there is no data on CURRENTWEEK-2 for an project, it is important the the row is still displayed, only that cel is then empty.
I tried to create different DAX measures to get this working. However, I'm having issues with the rows that have no data for a specific week, they are not displayed in the table.
DAX measure that I tried
Option 1
General-2 =
CALCULATE (
MAX ( 'Delivery data'[GeneralScore] ),
ALLEXCEPT ( 'table',
'Delivery data'[Account],
'Delivery data'[Project],
'Delivery data'[Comment]),
'Delivery data'[Weeknumber] = WEEKNUM(TODAY(), 21) -2
)
Option 2
General-2 =
CALCULATE (
MAX( 'table'[GeneralScore] ),
ALLEXCEPT ( 'table', 'table'[Project]),
'table'[Weeknumber] = WEEKNUM(TODAY(), 21) - 2
)
Both options have the same issue: no rows are displayed if there is no data on week CURRENTWEEK-2 for a team.
Could some please help with this issue? I'm really curious what I'm doing wrong. Please let me know if you need additional information.
Thanks in advance!
