Calculate values outside of filter context - Powerbi

Question

I have a few tables in Azure Analysis Model as shown below. I am using PowerBI to write a few measures.

DimProducts

+-----------------+--------+
| ProductId       | Name   |
+-----------------+--------+
|               1 |     p1 |
|               2 |     p2 |
|               3 |     p3 |
|               4 |     p4 |
|               5 |     p5 |
|               6 | `   p7 |
+-----------------+--------+

FactSales

+-----------+-------------+----------+
| ProductId |    Date     | Quantity |
+-----------+-------------+----------+
|         1 | 2016-02-01  |        1 |
|         1 | 2016-02-01  |        5 |
|         1 | 2016-02-05  |        7 |
|         2 | 2016-02-05  |        7 |
|         2 |  2016-02-08 |       10 |
+-----------+-------------+----------+

I need to find the sum quanity of sales. I need to find the sum of quantity sold for the max date outside the filter. The date filter for example is 2016-02-08. The max date below this filter is 2016-05-02 and the sum quantity for productid 2 is 7(4th line in factSales table). How could i write a measure for this?

Alexis Olson Alexis Olson · Accepted Answer · 2018-09-19T15:17:41

Calculate the maximal date prior to the date selected and then use that to replace your filter context inside CALCULATE.

MaxDateSum =
VAR SelectedDate = MAX ( FactSales[Date] )
VAR MaxDate =
    CALCULATE (
        MAX ( FactSales[Date] ),
        FILTER ( ALL ( FactSales[Date] ), FactSales[Date] < SelectedDate )
    )
RETURN
    CALCULATE ( SUM ( FactSales[Quantity] ), FactSales[Date] = MaxDate )

Calculate values outside of filter context - Powerbi

1 Answers