cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RahulPradhan25
Frequent Visitor

Snapshot Fact with complex filters

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 IdStageDateSale Value
ACC-00000016-SOSST8/11/2020124293
ACC-00000016-SOSB8/01/2020124293
ACC-00000058-SOSDP5/11/2020194559.47
ACC-00000058-SOSNA19/06/2020194559.47
ACC-00000058-SOSIDP3/12/2020194559.47
ACC-00000058-SOSCB16/12/2020194559.47
ACC-00000058-SOSPP13/07/2020194559.47
ACC-00000058-SOSCB19/06/2020194559.47
ACC-00000058-SOSCB12/06/2020194559.47
ACC-00000058-SOSB8/01/2020194559.47
ACC-00000142-SOSCB13/06/202042232.62
ACC-00000142-SOSCB10/06/202042232.62
ACC-00000142-SOSCB8/11/202042232.62
ACC-00000142-SOSNA23/06/202042232.62
ACC-00000142-SOSCB22/06/202042232.62
ACC-00000142-SOSNA11/06/202042232.62
ACC-00000142-SOSNA17/06/202042232.62
ACC-00000142-SOSB8/01/202042232.62

 

 

Any help will be highly appreciated.

 

Thanks,

Rahul.

2 REPLIES 2
amitchandak
Super User
Super User

@RahulPradhan25 , Please refer if this SCD(Slowly Changing Dimensions) video from Guyinacube can help

https://www.youtube.com/watch?v=tKeaQpWynzg



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
RahulPradhan25
Frequent Visitor

Hi,

 

Just sending the table again as it was not clear in last message:

 

Untitled.png

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors