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

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