Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Sum of revenue last available date in filtered date context

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.

IDDate Revenue
11/01/20210
21/01/20210
31/01/20210
12/01/20215
22/01/20210
32/01/202110
13/01/202110
23/01/20215
33/01/202110
14/01/202120
34/01/202130
15/01/202125

 

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

 

 

 

 

1 ACCEPTED SOLUTION
stevedep
Memorable Member
Memorable Member

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:

stevedep_0-1614919573525.png

File is attached. Enjoy!

Kind regards, 

 

Steve. 

 

View solution in original post

1 REPLY 1
stevedep
Memorable Member
Memorable Member

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:

stevedep_0-1614919573525.png

File is attached. Enjoy!

Kind regards, 

 

Steve. 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.