powerbidaxpowerquerydata-transform

Calculate a difference measure in Power BI based on two selected dates


At the moment I'm calculating a measure, which desribes the value-difference between a report date and the day before the selected report date. That means, if a user select today, then it shows the difference between the actual day and yesterday of that value. The report date can be selected by the user up to two years in the past.

enter image description here

The value difference is calculated as the following:

diffValue = SUM(table2[value])-[yesterdayValue]

The yesterdayValue is calculated like this:

yesterdayValue = 
var SelDate = SELECTEDVALUE(table1[valuationtime])
var PrevDate = CALCULATE(MAX(table1[valuationtime]), FILTER(ALL(table1), table1[valuationtime] < SelDate))
var result = CALCULATE(SUM(table2[value]), REMOVEFILTERS(table1[valuationtime]), table1[valuationtime] = PrevDate)
return result

This gives me the daily difference between a report date that was preselected at any time.

Now I would prefer to adapt the whole thing and give the user two dates to select. On the one hand, the report date as before and, on the other hand, the reference date (at the moment this is the day before the report date in my calculation). The user would therefore have two date selections in front of them and receive the difference in the value between the report date and the reference date. Both date selections access the same column of a table.

How can I solve this problem?

EDIT: Here you can see the two tables and how they connected: enter image description here

table1:

enter image description here

table2 has the same valuationtime-column.


Solution

  • You are going to need to have a separate "date" table for your second slicer. Create a new Calculate Table with:

    table1 (ref) = 'table1'
    

    Decide if you need to add any relationships to other tables, but you don't need one to table2.

    Then have these measures:

    Report value = SUM(table2[value])
    
    
    Reference value = 
      var refDate = DISTINCT( 'table1 (ref)'[valuationtime] )
      return
        CALCULATE(
          [Report value],
          REMOVEFILTERS('table1'),
          'table2'[valuationtime] IN refDate
        )
    
    
    DiffValue = [Report value] - [Reference value]