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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Joe_Grosskopf
Helper II
Helper II

Need to calculate latest entry for a day on a customer

We have quotes to customers updated many times a day. I'm trying to get a table that shows the latest update for each customer for each day. I managed to get a table that shows me the latest update for each day but NOT for each customer for each day. Just need to add another grouping or filter but don't know how... This is what I have for the table:

 

Latest_Opp_Amount_for_Day =
FILTER (
    'Opportunity History',
    VAR day_ =
        DATEVALUE ( 'Opportunity History'[CreatedDate] )
    VAR latest_ =
        CALCULATE (
            MAX ( 'Opportunity History'[CreatedDate] ),
            DATEVALUE ( 'Opportunity History'[CreatedDate] ) = day_,
            ALLEXCEPT ( 'Opportunity History', 'Opportunity History'[CreatedDate])
        )
    RETURN
        latest_ = 'Opportunity History'[CreatedDate]
)
 
And no errors, just just shows me the latest entry for that day. If there was a column called customer ID, how would I add it to show me the latest entry for the customer for that day?
2 REPLIES 2
Joe_Grosskopf
Helper II
Helper II

 Thank ou for the reply...the issue is the customer id is in a table other than datetable and I cannot seem to find how to add another allexcept when doing two tables. I have tried

 

Measure = calculate(sum('Opportunity'[Amount]),allexcept(
                                                    DateTable,
                                                    'DateTable'[Year-Day]
                                                        ),
                                                allexcept(
                                                    'Opportunity History',
                                                    'Opportunity History'[ID]
                                                ))
 
But it is not working
lukiz84
Memorable Member
Memorable Member

Add the customer to ALLEXCEPT

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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