Sum of revenue last available date in filtered date context

[Anonymous]

Hi everyone,

I have been struggling for quite a while on following calculation:

I have a dataset for which a row is generated for every ID on every day of its lifecycle.

As you can see below for ID = 2 its lifecycle stopped at 03/01/2021 so no longer appears onwards.

ID	Date	Revenue
1	1/01/2021	0
2	1/01/2021	0
3	1/01/2021	0
1	2/01/2021	5
2	2/01/2021	0
3	2/01/2021	10
1	3/01/2021	10
2	3/01/2021	5
3	3/01/2021	10
1	4/01/2021	20
3	4/01/2021	30
1	5/01/2021	25

I want to able to sum the revenue (actuals) for the lastest available date taking into account the dates selected in slicer from my datetable (1-* with my fact table here above).

Slicer selection : 02/01/2021 and 04/01/2021

Expected result: 02/01/2021 = 5 + 0 + 10 = 15

                          04/01/2021 = 20 + 5 (latest available value for ID =2 on 03/01/2021) + 30 = 55

Current result: 50 , so it does not take into account the value on 03/01/2020 (5)

With my current formula (see below) I am able to calculate this on row level in my matrix but the total is 50 instead of 55.

Actuals =

VAR Maxd =

MAXX (

FILTER (

ALLEXCEPT (

workorders,

workorders[businessUnitName],

workorders[SiteName],

workorders[accountManagerName],

workorders[customerName],

workorders[workorder_id]

),

workorders[Date] <= MAX ( Dim_Date_View[Calendar_Date] )

&& YEAR ( workorders[Date] ) = YEAR ( NOW () )

),

[Maxdate]

)

RETURN

CALCULATE (

SUM ( workorders[recognisedRevenue] ),

FILTER (

ALLEXCEPT (

workorders,

workorders[businessUnitName],

workorders[SiteName],

workorders[accountManagerName],

workorders[customerName],

workorders[workorder_id]

),

workorders[Date] <= MAX ( Dim_Date_View[Calendar_Date] )

&& YEAR ( workorders[Date] ) = YEAR ( NOW () )

),

workorders[Date] = Maxd

)

I think I need something with Sumx, but I tried and it still doesn't work.

Can anyone help me out with this?

Thanks!!

[stevedep · Accepted solution]

Hi,

One way to do this is like below (mind its using an unrelated date table).

SumLatestVal = 
VAR _selDate = SELECTEDVALUE(DimDate[Date].[Date])
VAR _TblMaxDate = 

    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE('Table',[ID],"MD", MAX([Date])),
                "R", LOOKUPVALUE('Table'[Revenue],'Table'[ID],[ID],'Table'[Date].[Date],[MD]))
            
            , FILTER('Table',[Date].[Date] <= _selDate))
RETURN
SUMX(_TblMaxDate,[R])

btw: I also added a debug measure. Pls see below:

File is attached. Enjoy!

Kind regards, 

Steve.