Previous date where there is a value

[paddyg95]

Hi,

I am trying to write a measure which looks for the last date where there is a value greater than 0. 

I have a calendar table linked on date to a timesheet hours table. In the below table I have got my target measure in the 'Previous Date with Hours > 0', which would find the last date (and before the current date) where the Hours is greater than 0. So that means on Date 13/05 where there is 0 Hours, the Previous Date with Hours > 0 is 06/05, and on 20/05 the Previous Date with Hours >0 is also 06/05, because of the 0 hours in the previous week of 13/05.

Hopefully that makes sense!

Many thanks for any help

Date	Hours	Previous Date with Hours > 0
01/04/2024	5752
08/04/2024		01/04/2024
15/04/2024	6407	01/04/2024
22/04/2024	6635	15/04/2024
29/04/2024		22/04/2024
06/05/2024	6130	22/04/2024
13/05/2024		06/05/2024
20/05/2024	6402	06/05/2024
27/05/2024	5443	20/05/2024
03/06/2024	6091	27/05/2024
10/06/2024	6543	03/06/2024

[ValtteriN]

Hi,

Here is one way to do this:

Measure 27 =

var _date = MAX('Table (39)'[Date])

RETURN

CALCULATE(MAX('Table (39)'[Date]),ALL('Table (39)'),'Table (39)'[Hours]>0 && _date >'Table (39)'[Date])


End result:
The measure returns same values as your example.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[paddyg95]

Hi @ValtteriN 
thank you for getting back to me, much appreciated. That does achieve the correct result if it is for a single table, but this wouldn't work if I get the date from the date table. The model would be something like the below with the timesheets table linked to the date table (here called Calendar). Do you know a way to achieve the same result but using a linked date table?

Many thanks 

[ValtteriN · Accepted solution]

Hi,

You can replace the table with calendar reference so like this:

Measure 27 =

var _date = MAX('Calendar'[Date])

RETURN

CALCULATE(MAX('Table (39)'[Date]),ALL('Calendar'),ALL('Table (39)'),'Table (39)'[Hours]>0 && _date >'calendar)'[Date])

[paddyg95]

Thank you @ValtteriN  , much appreciated !