Forum Discussion

clock0928's avatar
clock0928
Icon for Helper I rankHelper I
2 years ago
Solved

Identify the last entry within a measure

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 examp...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
clock0928
Icon for Helper I rankHelper I
2 years ago

Anonymous 
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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      clock0928
      Icon for Helper I rankHelper I
      2 years ago

      Anonymous 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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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