Hi all,
I am new to DAX and I am trying to create a dynamic weekly execution dashboard to track and count each test item’s status for each week. If some test items did not any update in coming week, the measure should continue use previous week’s test item’s status to perform the calculation (aka carry forward item’s test status from previous week)
In my Weekly Matrix Table, I was able to create a measure to calculate each item last test status in Row grant total, however I was unable to get the item’s last status return in each work week (WW) as showed in below table.
Below is the expected Weekly Matrix output table I looked for (highlighted in yellow).
In addition, I also hope I can use this measure’s return value to calculate total test status for each week as showed in below table.
Attached my WIP PBIX file.
Weekly_LastStatus_Count.pbix (42.1 KB)
Appreciate your guidance. Thanks in advance !
Regards
HK
Weekly_LastStatus_Count.pbix (41.7 KB)
I would attempt to solve this in the ETL process, I wrote some M Code to produce a table in the required granularity, if possible this would be best to do as close as you can to your data source, but if you have to do it power query try a few different ways, it would probably be best to merge a list of weeks from a date table to make sure you include all weeks in your data set, and then find a way to create the holes in your data.
Once that is done, you can create a matrix, if you create a second matrix for the count, and create a proper measure it will display the count per week per status, I added the column just to show the result, but you should model it properly in the final result.
Hello @rocio07
Did the responses above help solve your query?
If not, can you let us know where you’re stuck and what additional assistance you need?
If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION
Thank you
Hi @rocio07
Just following up if you still require assistance to solve your inquiry.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @rocio07
Due to inactivity, a response on this post has been tagged as “Solution”.
If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.