Quick measure - Differences between averages per category for a visual

[Anonymous]

Hi! I have a table of a waiting list for a product. Let's say the table is called <WaitingList> and the columns that matter are:

1. A category: 'Week' which is the status of the petition in that week. Note: this is a text field, not a date type.

2. A numeric: 'Delay' which is the days the customer has been waiting until that week.

I want to create a visual table where I can show changes between weeks. Adding the weeks and the average of delays for every week is straightforward:

However, now I want to show the differences between the average for a week and the previous (lag) week.

This is very easy in excel. Also, I know how to do it by creating a new table model with DAX with some intermediate columns. However, I want the option to update the table with some slicers in the report and I don't know if this is possible to implement using a new table model.

Someone could help me how to create a quick measure or a custom measure that calculates the differences in the average of 'Delay' for each week - the average for the previous week? So I can just use as an input a visual table object.

If this is not possible, how can I use DAX to tell the custom table model to update based on filter selections in a page report?

[Anonymous · Accepted solution]

I found a solution based on this post: Solved: Calculate difference between categories - Microsoft Power BI Community

I post it here in case someone else has the same issue:

difAverageDelay=

Var _last = maxx(filter(allselected(WaitingList), WaitingList[Week] < max(WaitingList[Week])), WaitingList[Week])

return

AVERAGE(WaitingList[Delay]) - calculate(AVERAGE(WaitingList[Delay]) , FILTER (allselected(WaitingList), WaitingList[Week] = _last))

[NickolajJessen]

Take a look at the file: "Department increase from previous date"
It resembles your query a fair bit.
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg

[Anonymous]

Thank you for your help. However, I don't know why you created 4 different vars. That seems to bit quite unstable. I found a solution with only one var to compute the max value.