Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am having a situation similar to "Snapshot fact table" but with a bit of complexity. Please bear with me as I explain. I have a number of Customer IDs having unique Sale value. After the creation of any customer, At any given date, one customer sits in one stage. It can jump from one stage to another on any date. We have several stages. The table that I have just notes the date of jump, if any, for any customer id. So the new customers gets added(and they have a default initial stage called "B" ) and they can jump from one stage to another at any coming dates.
What I want to create is a DAX formula which can sum up the sale values at any given date (the date might not be in the table below as it just notes the date of change of stages) for only specifc stages(for example stage- CC, IDP). So, if the user selects any date in reports, say he selects 20th Feb 2021, the dax sees what all customers have been in the table before and at this date, filters all the customers with stage - CC and IDP and sums up the sale values and give as a result.
Please see table below:
Cust Id | Stage | Date | Sale Value |
ACC-00000016-SOS | ST | 8/11/2020 | 124293 |
ACC-00000016-SOS | B | 8/01/2020 | 124293 |
ACC-00000058-SOS | DP | 5/11/2020 | 194559.47 |
ACC-00000058-SOS | NA | 19/06/2020 | 194559.47 |
ACC-00000058-SOS | IDP | 3/12/2020 | 194559.47 |
ACC-00000058-SOS | CB | 16/12/2020 | 194559.47 |
ACC-00000058-SOS | PP | 13/07/2020 | 194559.47 |
ACC-00000058-SOS | CB | 19/06/2020 | 194559.47 |
ACC-00000058-SOS | CB | 12/06/2020 | 194559.47 |
ACC-00000058-SOS | B | 8/01/2020 | 194559.47 |
ACC-00000142-SOS | CB | 13/06/2020 | 42232.62 |
ACC-00000142-SOS | CB | 10/06/2020 | 42232.62 |
ACC-00000142-SOS | CB | 8/11/2020 | 42232.62 |
ACC-00000142-SOS | NA | 23/06/2020 | 42232.62 |
ACC-00000142-SOS | CB | 22/06/2020 | 42232.62 |
ACC-00000142-SOS | NA | 11/06/2020 | 42232.62 |
ACC-00000142-SOS | NA | 17/06/2020 | 42232.62 |
ACC-00000142-SOS | B | 8/01/2020 | 42232.62 |
Any help will be highly appreciated.
Thanks,
Rahul.
@RahulPradhan25 , Please refer if this SCD(Slowly Changing Dimensions) video from Guyinacube can help
https://www.youtube.com/watch?v=tKeaQpWynzg
Hi,
Just sending the table again as it was not clear in last message:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |