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

     

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

 

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

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