Finding the most recent value based on selected period

[parry2k]

I have a very simple table:

- Calendar Table

- Job Table 

Job	Date	Completed
107	1/1/2017	1.44
107	4/1/2017	10
107	11/1/2017	8
106	4/1/2017	20
106	8/1/2017	15

When a user selected a year/month from slicer, i want the latest completed value

for example, if nov 2017 is selected, i want 

107 - 8 (most recent is Nov 1)

106 - 15 (most recent is Aug 1)

if May 2017 selected

107 -10 (most recent is Apr 2017

106 - 20 *most recent is apr 2017

Thanks,

[Zubair_Muhammad · Accepted solution]

Hi champ@parry2k

How about this MEASURE

MostRecent =
VAR selectedDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR LatestDate =
    CALCULATE (
        MAX ( JobTable[Date] ),
        FILTER ( ALLEXCEPT ( JobTable, JobTable[Job] ), JobTable[Date] <= selectedDate )
    )
RETURN
    CALCULATE (
        SUM ( JobTable[Completed] ),
        FILTER ( ALLEXCEPT ( JobTable, JobTable[Job] ), JobTable[Date] = LatestDate )
    )

[Zubair_Muhammad · Accepted solution]

Hi champ@parry2k

How about this MEASURE

MostRecent =
VAR selectedDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR LatestDate =
    CALCULATE (
        MAX ( JobTable[Date] ),
        FILTER ( ALLEXCEPT ( JobTable, JobTable[Job] ), JobTable[Date] <= selectedDate )
    )
RETURN
    CALCULATE (
        SUM ( JobTable[Completed] ),
        FILTER ( ALLEXCEPT ( JobTable, JobTable[Job] ), JobTable[Date] = LatestDate )
    )

[Zubair_Muhammad · Accepted solution]

It seems to work with your sample data using Calendar Date as slicer