last week count not working properly

[Dwivedi15]

I have a date table which formulates all the dates for given field.

Now i have a field which says- task end date through which i calculate last week tasks- for some reason total tasks are coming exact but not exact when go for individual category.

In power BI its coming as : 

The last week tasks are counted using :

total tasks Last week =

Var CurrentWeek=Weeknum(today())

var CurrentYear=Year(today())

Var MaxWeekNumber=Calculate(max('Date'[Week Number]),all('Date'))

return

sumx(filter(all('Date'),if(CurrentWeek=1,'Date'[Week Number]=MaxWeekNumber && 'Date'[Year]=CurrentYear-1,

'Date'[Week Number]=CurrentWeek-1 && 'Date'[Year]=CurrentYear)),[Total Tasks_filtered])

Total Tasks_filtered =

CALCULATE(COUNTROWS('Task Volumes'),'Task Volumes'[Project Ref]<>"TOOLING_CHECK" && 'Task Volumes'[Project Ref]<>"VAN_PLANT" && 'Task Volumes'[Management Area]<>"T.POPE")

This measure- Total_tasks_filterd is working perfect when counting all rows for last week :

I am not sure why this is happening, i am not able to debug. Any help would be appreciated.

Many thanks!

[Greg_Deckler]

@Dwivedi15 In response to your private message. One thing I noticed immediately is that in your Date table, you only have 6 dates for last week (7/2/2023). Looks like you are missing Saturday the 8th. Perhaps this is causing some of your problem?

[Dwivedi15]

Hi greg,

PBIX- https://drive.google.com/file/d/1faZg-lGf7mCdmayB3VfHwrg9-VeW1_Rn/view?usp=sharing

Excel - https://docs.google.com/spreadsheets/d/1ncfajPPnBrDKh9moN7N5Tco7EZYqeOv-/edit?usp=sharing&ouid=10989...

Excel for just last week: https://docs.google.com/spreadsheets/d/1E3NAOYTqcfqmQSDpNEYI-ykK3U8AsS0v/edit?usp=sharing&ouid=10989...

If you see here indiviual count of tasks are not coming correct but overall is coming right. Also total aborts works just fine.
REally cant understand why !!!?

[lbendlin]

Your MaxWeekNumber calculation needs to be corrected to calculate the proper max week of the prior year ( for current week =1 )

[Dwivedi15]

Can you please elaborate? i did not get you!

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Dwivedi15]

how can i share my PBIX and Excel file ?

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Dwivedi15]

Hi,

PBIX- https://drive.google.com/file/d/1faZg-lGf7mCdmayB3VfHwrg9-VeW1_Rn/view?usp=sharing

Excel - https://docs.google.com/spreadsheets/d/1ncfajPPnBrDKh9moN7N5Tco7EZYqeOv-/edit?usp=sharing&ouid=10989...

Excel for just last week: https://docs.google.com/spreadsheets/d/1E3NAOYTqcfqmQSDpNEYI-ykK3U8AsS0v/edit?usp=sharing&ouid=10989...

If you see here indiviual count of tasks are not coming correct but overall is coming right. Also total aborts works just fine.
REally cant understand why !!!?

[Dwivedi15]

Excel file for all data from 2021 Jan: https://docs.google.com/spreadsheets/d/160A5HKN1xuI-3UgQgFK0npHJuwRFX_qc/edit?usp=sharing&ouid=10989...

Excel file for just last week: 
https://drive.google.com/file/d/1xN6-74xf1tayMtgb9jJeS5zZ_B1byPAD/view?usp=sharing

PBIX file :
https://drive.google.com/file/d/1xN6-74xf1tayMtgb9jJeS5zZ_B1byPAD/view?usp=sharing

If you see, here last week count is not coming correct for individual 'Management Area' for both count of tasks and Aborts.

Kindly help me figure this out.

Many Thanks in advance!!

[Dwivedi15]

Hi , This week rank is not working out for me, now even the overall number is not coming correct!

[amitchandak]

@Dwivedi15 ,

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

[Dwivedi15]

Hi This does not work for me, as now even the total number of rows arent coming out correct!