summarise max wait by team and snapshot date

[hpatel24779]

Hi All,

I have a table with team, wait weeks and snapshot date. i have calculated the max wait week per team and month. i now want to work out the difference in max week from current month to previous month. then for the latest month, count how many teams have max wait of 0, greater than 0 and less than 0.

this is my table and my measure is:

to calculate the current month max wait = 

current =

var startdate = FIRSTDATE('01 - Calendar'[Date])

var enddate = LASTDATE('01 - Calendar'[Date])

RETURN

CALCULATE(MAX('03 - Assessment Waits'[wait_weeks]),'03 - Assessment Waits'[Inserted On Date]='03 - Assessment Waits'[End of Current Month])

previous max wait = 

Previous =

var startdate = FIRSTDATE('01 - Calendar'[Date])

var enddate = LASTDATE('01 - Calendar'[Date])

RETURN

CALCULATE(MAX('03 - Assessment Waits'[wait_weeks]),'03 - Assessment Waits'[Inserted On Date]='03 - Assessment Waits'[End of Prev Month])

then to get the difference:

My measure = '00 - Measures'[current]-[Previous]

then to count how many are 0, >0 and <0

count = IF([My measure]>0,"Greater than 0",IF([My measure]=0,"Equals 0","Less than 0"))

however this isn't working as it should.

my finished table would look like this:

Max Wait Week	Count
Equals 0	3
Greater than 0	4
Less than 0	6

Can somebody please help as i have been trying to get this right for a day with no luck.

kind regards

Hetal

[v-aatheeque]

Hi @hpatel24779 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @hpatel24779 ,

If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @hpatel24779 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[danextian · Accepted solution]

Hi @hpatel24779 

Please provide a workable sample data (not an image) and not in a pivoted format as in your image, your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.