Search code examples
sql-serverssasdaxdata-analysistabular

dax for events in progress at end of period using date table

I need to sum up total dollars of all open opportunities by date. Each record has a [valid from] and a [valid to] date attached to it. When an opportunity gets updated in the DB, a new line is added with a [valid from] date = today and the [valid to] date = end of time (12/31/2100). The previous line's [valid to] date gets yesterday's date.

Reasoning: because an opportunity can be changed many times per day, we only care about the version from each day that crosses the 11:59:59 threshold. When an opportunity gets changed, the previous version was only valid up to the end of the previous day, so it gets the previous day's date for its [valid to] field.

When broken down by units larger than date, I need the last correct version of the opportunity at that time. E.g., the dollar amount for the month of January is the amount from the most recent dollar value of the opportunity on 1/31 at 11:59:59.

Here is a simplified version of my schema:

Imgur

Here is some sample data:

Opp Number  Dollars Opp Created Date    Header Valid From   Header Valid To
1           100         1/1/2019        1/1/2019            1/3/2019
1           50          1/1/2019        1/4/2019            1/5/2019
1           75          1/1/2019        1/6/2019            12/31/2100
2           200         1/3/2019        1/3/2019            1/4/2019
2           210         1/3/2019        1/5/2019            1/6/2019
2           250         1/3/2019        1/7/2019            12/31/2100

Given this example data set, the pivot table should look like this:

Dates   Sum of Dollars
1/1/2019    100
1/2/2019    100
1/3/2019    300
1/4/2019    250
1/5/2019    260
1/6/2019    285
1/7/2019    325
1/8/2019    325
...
1/31/2019   325
January     325

I have tried several approaches, but gotten nowhere. Here are the references that I have tried:

I was able to get this to work as expected in dax studio, but I can't translate it into a measure.

EVALUATE
ADDCOLUMNS (
    VALUES ( 'DIM Date'[Date]),
    "Open Orders",
    SUMX (
        FILTER (
            GENERATE (
                SUMMARIZE (
                    'FACT Opportunity',
                    'FACT Opportunity'[header valid from],
                    'FACT Opportunity'[header valid to],
                    "Rows",
                    DISTINCTCOUNT ( 'FACT Opportunity'[Opp Number])
                ),
                DATESBETWEEN (
                    'DIM Date'[Date],
                    'FACT Opportunity'[header valid from],
                    'FACT Opportunity'[header valid to]
                )
            ),
            'DIM Date'[Date] = EARLIER ('DIM Date'[Date])
        ),
        [Rows]
    )
)
Solution

  • I figured it out. I was missing an ALL() function to eliminate context transition done by the CALCULATE() function. I also used LASTDATE() around the VALUES() function to get the last date in any given context. That gives me the sum for the last date in any given month/qtr/year in the hierarchy.
    Here is the code i used:

    Active Opps:=
SUMX(
    LASTDATE( VALUES ( 'DIM Date'[Date] ) ),
    VAR CurrentDate = 'DIM Date'[Date]
    VAR OrderedBeforeCurrentDate =
        FILTER (
            ALL ( 'FACT Opportunity'[header valid from]), 
            'FACT Opportunity'[header valid from] <= CurrentDate 
        )
    VAR ShippedAfterCurrentDate =
        FILTER (
            ALL ( 'FACT Opportunity'[header valid to] ), 
            'FACT Opportunity'[header valid to] >= CurrentDate 
        )
    RETURN
        CALCULATE (
            DISTINCTCOUNT( 'FACT Opportunity'[Opp Number]),
            OrderedBeforeCurrentDate,
            ShippedAfterCurrentDate,
            ALL ( 'DIM Date' )
        )
)

    Found the pattern here: https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/