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

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.