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

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