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

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

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

 

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

 

  • 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