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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Sylvain74
Helper III
Helper III

How to compare data at 2 different dates

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
        )
    )

yingyinr_0-1639465287502.png

Best Regards

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

@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. 

ContractDeliveries.pbix

Anonymous
Not applicable

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
        )
    )

yingyinr_0-1639465287502.png

Best Regards

Sylvain74
Helper III
Helper III

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.

 

2021-12-11_12h24_25.png

 

Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Sylvain74
Helper III
Helper III

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.

Have you tried the approach proposed by @parry2k  ?

I read the article but I did not apply it to my scenario yet...

parry2k
Super User
Super User

@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.

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.