Identify the last entry within a measure

[clock0928]

Hey everyone,

I am trying to work out how to have one of my measures (Previous Work On Hand) identify the last entry of each week, where that week is 2 weeks prior to audit date.

So for example (let me paint a picture) - 

- If I am completing audits on Monday the 11th of March 2024 (week 11), the week prior (week 10) has "Current Work On Hand" balance (1x entry for the week, on a Friday), and then 2 weeks prior (week 9) has "Previous Work On Hand" balance (2x entries for the week**).

**Now this, like Current Work On Hand would usually be 1 entry on the Friday of the week, however this has 2 entries (which is not a typical week), because it was a week that is the end and start of the month. One will be on the last day of the month (29/02/2024) and then another on the Friday of the week (01/03/2024).

I am trying to work out, how to amend my current measure (below) to identify the last entry for the WOH where there would potentially be 2 in one week.

msrLastWeek_WOH_AppleTasks = 
CALCULATE(
    SUM('Work On Hand'[AppleTasks]),
        'Calendar'[WeekOfYear] = SELECTEDVALUE('Calendar'[WeekOfYear]) - 1, ALL('Calendar')
    )

Please find below a rough example (with data set) of this example (and an example of a normal week for comparison).

Currently, my measure will sum the 2 entries for the week together, where I only need to have the last entry.

If there are any questions in relation to my data, please let me know and I will assist.

All assistance will be greatly appreciated! 🙂

[RossEdwards]

My suggestion is to make sure you have a Date Table in your model and ensure one of the columns in your date table denotes week numbers.  You should be able to configure this table on what day of the week is the start of your week.

From here you could write a measure that looks for the value with the MAX date for provided date context.

Put that measure into a table using your Weeknumbers (and year) as the visuals first couple of columns, that should give you a last audit each week.

[clock0928]

I believe to have something now, however it is playing games with me still...
This is the DAX I have created - 

1testmsrPWOH_AbnormalWeek_Tasks = 
CALCULATE(
    LASTNONBLANK('Work On Hand'[AppleTasks], 1),
    'Calendar'[WeekOfYear] = SELECTEDVALUE('Calendar'[WeekOfYear]) - 1,
    ALL('Calendar')
)

However, when I use it on my table visual, it is showing the incorrect data for just one single team member. In addition to that, the "Total" at the bottom is not adding up correctly...

@RossEdwards  - would you happen to know of any reason with that?
Or any other users that could provide assistance (@amitchandak  - if you have some time available?)

[RossEdwards]

LASTNONBLANK looks for the last value in the list of values.  Once the data is inside your model, you won't know what order the column is.  Looking at your data, i would assume that the tasks are just naturally recorded on the days they are completed, so i'd expect you to SUM them?

Here is what I expect you'd want something like:

1testmsrPWOH_AbnormalWeek_Tasks = 
var selectedDate = MAX('Calendar'[Date])
var selectedWeek = CALCULATE(SELECTEDVALUE('Calendar'[WeekOfYear]), 'Calendar'[Date] = selectedDate
var selectedYear = CALCULATE(SELECTEDVALUE('Calendar'[Year]), 'Calendar'[Date] = selectedDate
var output = CALCULATE(
    SUM('Work On Hand'[AppleTasks]),
    ALL('Calendar'),
    'Calendar'[WeekOfYear] = selectedWeek,
    'Calendar'[Year] = selectedYear,
)
RETURN
output

This measure will always give you the week at the end of whatever date context it finds itself in.

[clock0928]

@RossEdwards 
Oh I see... Okay thank you, so I probably shouldn't be using "LASTNONBLANK"...
I have tried your DAX and I get the same little pesky error as I was receiving before - that is that one of my team members entries does not align for what they have entered for the date expected...

I don't quite understand why that would be... Screen shot of team member below - 

 

PWOH should return a number of "3", and I know this because all other numbers are returning from the date of the 02/02/2024 for PWOH.

 

[RossEdwards]

For your specific record issue, i'd look into the dataview in Power BI and pay close attention to the key fields of that record.  Make sure there isn't anything that could be filtering it out.  I'd suggest the devil in the detail there.

I thought of an issue in the logic of the code i've supplied to you.  When you get to the end of the calendar year, the "week" will be broken up into 2 different weeks.  That being the final days if the last partial week of the year and the first few days of the year in a new also partial week.  Here is a fix for that by always going to the end of the week and doing a -7 dates in period

1testmsrPWOH_AbnormalWeek_Tasks = 
var selectedDate = MAX('Calendar'[Date])
var selectedWeek = CALCULATE(SELECTEDVALUE('Calendar'[WeekOfYear]), 'Calendar'[Date] = selectedDate)
var selectedYear = CALCULATE(SELECTEDVALUE('Calendar'[Year]), 'Calendar'[Date] = selectedDate)
var filterDate = CALCULATE(
    MAX('Calendar'[Date]),
    ALL('Calendar'),
    'Calendar'[WeekOfYear] = selectedWeek,
    'Calendar'[Year] = selectedYear
)
var output = CALCULATE(
    SUM('Work On Hand'[AppleTasks]),
    ALL('Calendar'),
    DATESINPERIOD(
		'Calendar'[Date],
		filterDate,
		-7,
		DAY
	)		
)
RETURN
output

[clock0928]

@RossEdwards thank you for the amended version.
I've completed some testing with my data, and I could see that the previous "LASTNONBLANK" was pulling the larger number in that date range.
The DAX you have provided seems to be pulling from the WeekOfYear that my slicer is selected - what would I change to make it the week prior?

[RossEdwards · Accepted solution]

I'd just apply the DATEADD function and minus 7 days off the date filter result.  Here is a quick change i made without testing:

1testmsrPWOH_AbnormalWeek_Tasks = 
var selectedDate = MAX('Calendar'[Date])
var selectedWeek = CALCULATE(SELECTEDVALUE('Calendar'[WeekOfYear]), 'Calendar'[Date] = selectedDate)
var selectedYear = CALCULATE(SELECTEDVALUE('Calendar'[Year]), 'Calendar'[Date] = selectedDate)
var filterDate = DATEADD(CALCULATE(
    MAX('Calendar'[Date]),
    ALL('Calendar'),
    'Calendar'[WeekOfYear] = selectedWeek,
    'Calendar'[Year] = selectedYear
), -7, DAY)
var output = CALCULATE(
    SUM('Work On Hand'[AppleTasks]),
    ALL('Calendar'),
    DATESINPERIOD(
		'Calendar'[Date],
		filterDate,
		-7,
		DAY
	)		
)
RETURN
output

[clock0928]

@RossEdwards I deviated slightly from yours and this is what I have come to with a working calculation.

1testmsrPWOH_AbnormalWeek_Tasks =
VAR selectedDate = MAX('Calendar'[Date])
VAR lastDateOfWeekPrior = LASTDATE(
    DATESBETWEEN(
        'Calendar'[Date],
        selectedDate - 14,   -- Go back two weeks to ensure we cover the entire previous week
        selectedDate - 8     -- Go back one week to the end of the previous week
    )
)
VAR output = CALCULATE(
    SUM('Work On Hand'[AppleTasks]),
    ALL('Calendar'),
    'Calendar'[Date] = lastDateOfWeekPrior
)
RETURN
output

Thank you so much for your assistance with this, I wouldn't have reached the conclusion without your assistance!