Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
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.
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 name | Category 1 | Category 2 | Created on |
user 2 | approved | not approved | 5/1/23 |
user 1 | approved | not approved | 3/1/23 |
user 2 | approved | approved | 2/1/23 |
user 1 | not approved | Approved | 1/1/23 |
user 3 | not approved | approved | 2/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
UserName | Category 1 | category 2 | latest Created on |
user 2 | approved | approved | 2/1/23 |
user 1 | approved | not approved | 3/1/23 |
user 3 | not approved | approved | 2/1/23 |
and bar chart as
So far I have this measure w
thanks for your help 🙂
Genius, all of your assumptions were valid and it works like a charm!
Thank you!
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
83 | |
82 | |
67 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |