Measure to find sum between date ranges in another table

[Anonymous]

Hi All,

I would appreciate some help with the following..

I have three tables:

Table1:

StoreID	Begin	End
X	1-1-2024	3-1-2024
X	5-1-2024
Y	2-1-2024	5-1-2024
Y	7-1-2024	8-1-2024
Z	1-1-2024	4-1-2024

Table2:

StoreID	DateSold	Amount
X	1-1-2024	1
X	3-1-2024	2
X	8-1-2024	1
Y	2-1-2024	3
Y	6-1-2024	1
Z	1-1-2024	2
Z	7-1-2024	2

Table3 is just a table with unique Store ID's to create a connection between Table1 and Table2.

What I want to achieve is a total sum of Table2[Amount] where the Table2[DateSold] is between a period of Table1[Begin] and Table1[End] (or that Table1[End] is empty). Table1[Begin] is included and Table1[End] is excluded in the date range.

So for example, for StoreID = 'X', we have three transactions:

The transaction of 1-1-2024 with Amount = 1 falls within the period 1-1-2024 until 3-1-2024.
The transaction of 3-1-2024 with Amount = 2 does NOT fall within the period 1-1-2024 until 3-1-2024 (and is not after 5-1-2024).
The transaction of 8-1-2024 with Amount = 1 falls within the period 5-1-2024 until [empty].

For StoreID = 'X', the total of the new measure should be: 2.

Similarly:

For StoreID = 'Y', the total of the new measure should be: 3.

For StoreID = 'Z', the total of the new measure should be: 2.

In the end, I want to show a table like this:

StoreID	Measure
X	2
Y	3
Z	2
Total	7

How should I write a measure for this?

I tried a SUMX with filter to account for the date ranges but that measure only shows correct values when I have a table that includes the columns of Table1. Also, the total at the bottom row shows an incorrect value.

Thanks for your attention! 🙂

[Anonymous]

Hi  @Anonymous , 
@MFelix nice method！And here's my solution.

1.Use power query to merge table1 and table 2

2.Expand table2 column

3.Close and apply and create a measure

Measure = 
CALCULATE(
    SUM(Table1[Table2.Amount]),
    FILTER(
        Table1,
        (Table1[End] <> BLANK() && Table1[Table2.DateSold] >= Table1[Begin] && Table1[Table2.DateSold] < Table1[End])
        ||
        (Table1[End] = BLANK() && Table1[Table2.DateSold] >= Table1[Begin])
)
)

4.Final output

 

Best regards

Albert He

[MFelix]

Hi @Anonymous ,

This bring the problem of getting repeated information, in this case you can add on the PQ a flag column that checks if the value is within the time frame and then filter out the information and just make a simple sum, no need for complex dax.

[MFelix · Accepted solution]

Hi @Anonymous ,

Add the following two measures:

Sales in Period =
SUMX (
    ADDCOLUMNS (
        'Table',
        "SalesInPeridod",
            IF (
                'Table'[End] <> BLANK (),
                CALCULATE (
                    SUM ( 'Table (2)'[Amount] ),
                    FILTER (
                        'Table (2)',
                        'Table (2)'[DateSold] >= 'Table'[Begin]
                            && 'Table (2)'[DateSold] < 'Table'[End]
                    )
                ),
                CALCULATE (
                    SUM ( 'Table (2)'[Amount] ),
                    FILTER ( 'Table (2)', 'Table (2)'[DateSold] >= 'Table'[Begin] )
                )
            )
    ),
    [SalesInPeridod]
)


Total Sales In Period = SUMX('Table (3)', [Sales in Period])

The use the last measure for your visualization.

Spliting this into two measure to simplify the calculation.