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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

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.