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

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!