Forum Discussion

Sylvain74's avatar
Sylvain74
Helper III
4 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

12 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

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

  • Sylvain74's avatar
    Sylvain74
    Helper III
    4 years ago

    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?

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      Use the respective date tables as the basis and add the measures you created for either scenario.

      • Sylvain74's avatar
        Sylvain74
        Helper III
        4 years ago

        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.

  • Sylvain74's avatar
    Sylvain74
    Helper III
    4 years ago

    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.

     

     

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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.

    • Sylvain74's avatar
      Sylvain74
      Helper III
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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