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

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

     

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