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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
StephenClarke
Frequent Visitor

Dynamically filter last row per item

My 'Fact_Booking' table holds a log of every time a [Sales_ID] changes. I therefore have multiple rows per [Sales_ID] as sales are confirmed / revised / cancelled etc.

 

I would like to be able to set a date slicer that will dynamically filter my 'Fact_Booking' table to only show for each [Sales_ID] the max [LatestModifiedDate] row which is <= the slicer date.

 

Is there a measure I can write that will allow me to run a SUM on orders across a filtered Fact_Booking table returning only the max [LastModifiedDate] per [Sales_ID] that is <= the value I set on the 'LastModified date table' slicer?

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You could try

Sum of last rows =
VAR ChosenDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    CALCULATETABLE (
        INDEX (
            1,
            'Fact booking',
            ORDERBY ( 'Fact booking'[Last modified date], DESC ),
            PARTITIONBY ( 'Fact booking'[Sales ID] )
        ),
        'Date'[Date] <= ChosenDate
    )
VAR Result =
    SUMX ( SummaryTable, 'Fact booking'[Sales value] )
RETURN
    Result

This makes a few assumptions. Firstly that your date table is linked to your fact table on the last modified date. Secondly, you will need a unique identifier for each row in fact booking. If you don't have one naturally you can add an index column using power query. Make sure that the unique column is marked as the key column in the modelling view, that will stop INDEX complaining that the table may have duplicates.

View solution in original post

3 REPLIES 3
Mi_80
Helper I
Helper I

Hi, I am trying to produce something similar but this is not working for me at all .  I am told index is not a function?

 

I have an updates table that update a user status from approved, not approved in categories one and category 2. I also have a calendar table

e.g.

 

User nameCategory 1Category 2Created on
user 2approvednot approved5/1/23
user 1approvednot approved3/1/23
user 2 approvedapproved2/1/23
user 1not approvedApproved1/1/23
user 3not approvedapproved2/1/23

 

I would like to be able to display a bar chart with a date slider that can show for example the countof category one approved users on a chosen day...so if I filter to 4/1/23 then the count for category 1 approved user would be 2 (users 1and 2) and the count for category 1 not approved would be 1 (user3), count category 2 approved 2 (users 2 and 3), category 2 not approved 1 (user1) as it would only take into account the latest record for a user not the ones previously.  So data table filtered to 4/1/23 as below

UserNameCategory 1category 2latest Created on
user 2approvedapproved2/1/23
user 1approvednot approved3/1/23
user 3not approvedapproved2/1/23

 

and bar  chart as 

 

chart.png

So far I have this measure w

Measure =
CALCULATE(FIRSTNONBLANK(new_partnerlandscapehistory[createdon],1),
FILTER(new_partnerlandscapehistory,new_partnerlandscapehistory[createdon] = MAX(new_partnerlandscapehistory[createdon])))
 
which manages to give me the last record up to that date in a table but can't get this to work with a count in barchart as then it counts the previous records also

 

status.png

 

thanks for your help 🙂

StephenClarke
Frequent Visitor

Genius, all of your assumptions were valid and it works like a charm!

Thank you!

johnt75
Super User
Super User

You could try

Sum of last rows =
VAR ChosenDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    CALCULATETABLE (
        INDEX (
            1,
            'Fact booking',
            ORDERBY ( 'Fact booking'[Last modified date], DESC ),
            PARTITIONBY ( 'Fact booking'[Sales ID] )
        ),
        'Date'[Date] <= ChosenDate
    )
VAR Result =
    SUMX ( SummaryTable, 'Fact booking'[Sales value] )
RETURN
    Result

This makes a few assumptions. Firstly that your date table is linked to your fact table on the last modified date. Secondly, you will need a unique identifier for each row in fact booking. If you don't have one naturally you can add an index column using power query. Make sure that the unique column is marked as the key column in the modelling view, that will stop INDEX complaining that the table may have duplicates.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.