The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
I have a FactPosition table storing contracts and related quantity and every night a snapshot is created. I need to create a PowerBI report showing the data from snapshot taken at date 1 and data from snapshot taken at date 2.
Then I need to display the differences between the 2 snapshots.
I think I should have 2 differents slicers related to 2 different date tables listing the snapshot dates. But I am a bit lost since if I link those 2 date tables to the FactPosition table on the snapshot date field, then it will not return any records since the table cannot be filtered out on the same field on two different dates.
I cannot figure out what should be the correct data model and correct way to achieve that?
Thanks ahead for your help.
Sylvain
Solved! Go to Solution.
Hi @Sylvain74 ,
I updated your sample pbix file(see attachment), please check whether it can get your expected result.
1. Update the formula of measure [Scenario - Delivered Quantity ] as below
Scenario - Delivered Quantity =
VAR _selactdates =
ALLSELECTED ( 'Scenario Actual Date'[VALUATION_DATE] )
VAR _selscedates =
ALLSELECTED ( 'Scenario Dates'[VALUATION_DATE] )
RETURN
CALCULATE (
SUM ( 'Contract Deliveries'[DeliveredQuantity] ),
FILTER (
'Contract Deliveries',
'Contract Deliveries'[ValuationDate]
IN _selactdates
|| 'Contract Deliveries'[ValuationDate] IN _selscedates
)
)
2. Create a new measure as below to get the sum of contract quantity
Scenario - ContractQuantity =
VAR _selactdates =
ALLSELECTED ( 'Scenario Actual Date'[VALUATION_DATE] )
VAR _selscedates =
ALLSELECTED ( 'Scenario Dates'[VALUATION_DATE] )
RETURN
CALCULATE (
SUM ( 'Contract Deliveries'[ContractQuantity] ),
FILTER (
'Contract Deliveries',
'Contract Deliveries'[ValuationDate]
IN _selactdates
|| 'Contract Deliveries'[ValuationDate] IN _selscedates
)
)
Best Regards
@Sylvain74 very hard to see what is going on? If you can share pbix file it will help to look into it, remove sensitive information before sharing.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi Parry2k,
Please find here the pbix file. It is anonymized and filtered out on only 2 counterparties to reduce the volumen of data. In the dataset you will find multiple snapshots of purchase and sales contracts delivered quantity. The goal is to compare the delivered quantity by counterparty and and contract for different selected snapshot dates.
Hi @Sylvain74 ,
I updated your sample pbix file(see attachment), please check whether it can get your expected result.
1. Update the formula of measure [Scenario - Delivered Quantity ] as below
Scenario - Delivered Quantity =
VAR _selactdates =
ALLSELECTED ( 'Scenario Actual Date'[VALUATION_DATE] )
VAR _selscedates =
ALLSELECTED ( 'Scenario Dates'[VALUATION_DATE] )
RETURN
CALCULATE (
SUM ( 'Contract Deliveries'[DeliveredQuantity] ),
FILTER (
'Contract Deliveries',
'Contract Deliveries'[ValuationDate]
IN _selactdates
|| 'Contract Deliveries'[ValuationDate] IN _selscedates
)
)
2. Create a new measure as below to get the sum of contract quantity
Scenario - ContractQuantity =
VAR _selactdates =
ALLSELECTED ( 'Scenario Actual Date'[VALUATION_DATE] )
VAR _selscedates =
ALLSELECTED ( 'Scenario Dates'[VALUATION_DATE] )
RETURN
CALCULATE (
SUM ( 'Contract Deliveries'[ContractQuantity] ),
FILTER (
'Contract Deliveries',
'Contract Deliveries'[ValuationDate]
IN _selactdates
|| 'Contract Deliveries'[ValuationDate] IN _selscedates
)
)
Best Regards
Hello,
I tried the suggestion from Parry2K, as you can see in the print-screen, the measure works well, meaning it is calculated as per selected dates but the table is not filtered correctly. In the table I should not see rows where the date is not in the selection from "Scenario Dates".
I tried to create a dax table filtered out using the selected dates, but did not succeed.
Hi,
Share some data and show the expected result.
Hi Guys,
Thank you both for your help. Based on your examples, I will be able to create some measures based on the data at date 1 and date 2.
However I need as well to display as well the details of those data at date 1 in a table and at date 2 in another table. And on that point I still have a doubt.... How can I display those information if the date tables are disconnected from the fact table?
Use the respective date tables as the basis and add the measures you created for either scenario.
I understand that I can create measure which will react according to each disconnected time tables used in the slicers. But I don't see how the records of the fact table will be displayed.
I can have records present at t1 which are not present at t2, or vice versa and I can have records present in both snapshots t1 and t2.
I read the article but I did not apply it to my scenario yet...
@Sylvain74 based on what @lbendlin suggests, I have a blog post of a similar use case, you can read it here Compare Budgeted Scenarios vs. Actuals | PeryTUS IT Solutions and use it in your solution.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
You can achieve that by using disconnected tables to control your slicers. Then in your measure query the current value of the slicers and compute as needed.
User | Count |
---|---|
86 | |
84 | |
34 | |
34 | |
33 |
User | Count |
---|---|
94 | |
79 | |
63 | |
55 | |
51 |