Dynamically Sum between two datasets with multiple criteria

[mattwoldt]

Hello,

Having a bit a pickle on this one, but I am hoping someone here can potentially help. I just am not really sure how I can do this dynamically, but I need to have power bi sum up information based off of two different data sets. 

So, we have revenue and the revenue is broken up by the ID of said person generating revenue, the month number, the year and the amount of revenue. 

On a different data set we have those same ID's where it will show a Start Month, Start Year and End Month and End Year. 

What I'm trying to do is to tell Dax to only sum up the information that is between that Start Month/Start Year and the End Month/End Year. 

For instance:

Bill made 1,000,000 in 2019 - 2020

and 1,000,000 in 2021-2022
the Break down is such:

ID	Revenue Year	Revenue Month	Revenue
1234	2019	3	250000
1234	2019	6	250000
1234	2020	2	250000
1234	2020	7	250000
1234	2021	1	250000
1234	2021	6	250000
1234	2021	9	250000
1234	2022	3	250000

My other data set will look something like this:

ID	Start Month	Start Year	End Month	End Year	Revenue Year
1234	6	2020	6	2021	Year 1
1234	6	2021	6	2022	Year 2

How can I tell dax to dynamically sum up between that time period. So essentially, what I am trying to do is create the below:

ID	Start Month	Start Year	End Month	End Year	Revenue Year	Revenue
1234	6	2020	6	2021	Year 1	750,000
1234	6	2021	6	2022	Year 2	500,000

Please know that there are over 4000 ID's and years spanning from 2013 all the way to 2034, so if I can possibly make this dynamic I would be so happy. 

Thank you again!

[Ashish_Mathur]

Hi,

Show the exact result you are expecting.

[mattwoldt]

Sorry, re-edited my post, didn't realize I had some typos. Essentially I am wanting to do a dynamic sum if that looks at the rows year and month. Essentially it is sum revenue that is equal to the start year and greater than or equal to the month # in the revenue table + revenue that is equal to the end year and less than or equal to the end month #. 

[Ashish_Mathur]

Please explain how you arrived at 750,000 and 500,000 very clearly.

[danextian]

Hello @mattwoldt ,

What do you mean by this statement? There is no data below and the years in the second table are all 2020.

How can I tell dax to dynamically sum up between that time period. So essentiallyl where in the below data set we see Year 1 is between 2019 Month 6 and 2020 Month 6, this would sum up to $750,000 and then Year 2 would be $500,000.

[mattwoldt]

Sorry, re-edited my post, didn't realize I had some typos. Essentially I am wanting to do a dynamic sum if that looks at the rows year and month. Essentially it is sum revenue that is equal to the start year and greater than or equal to the month # in the revenue table + revenue that is equal to the end year and less than or equal to the end month #. 

[danextian · Accepted solution]

Hi @mattwoldt ,

Please take note that there is an overlap in your second table - 202106 is both the end period of Year 1 and start of Year 2.

To make the calcuation simple, I created several columns to help with the calculations.

Table1

Revenue YrMo = 
VALUE ( Table1[Revenue Year] & FORMAT ( Table1[Revenue Month], "00" ) )

Table2

Start YrMo = 
VALUE ( Table2[Start Year] & FORMAT ( Table2[Start Month], "00" ) )

End  YrMo = 
VALUE ( Table2[End Year] & FORMAT ( Table2[End Month], "00" ) )

For the revenue, you can either use a calculated column or a measure approach.

Calc column in table2

Revenue Sum (Calculated Column) = 
CALCULATE (
    SUM ( Table1[Revenue] ),
    FILTER (
        Table1,
        Table1[Revenue YrMo] >= EARLIER ( Table2[Start YrMo] )
            && Table1[Revenue YrMo] <= EARLIER ( Table2[End  YrMo] )
    ),
    Table1[ID] = EARLIER ( Table2[ID] )
)

Measure

Revenue Sum (Measure) = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Table2, Table2[ID], Table2[Start YrMo], Table2[End  YrMo] ),
        "x",
            CALCULATE (
                SUM ( Table1[Revenue] ),
                FILTER (
                    Table1,
                    Table1[Revenue YrMo] >= [Start YrMo]
                        && Table1[Revenue YrMo] <= [End  YrMo]
                        && Table1[ID] = [ID]
                )
            )
    ),
    [x]
)

Those columns/measure would result to this:

Please refer to this link for the sample pbix - https://drive.google.com/file/d/1_Iv8UAqk58yxQOEDsLFdS7l3fgjxIUOh/view?usp=sharing 

[danextian]

I'm glad that worked.

EARLIER returns the value of a row from a column. If the second argument is not specified, that defaults to the current row.

[mattwoldt]

Well I may have spoken a little too soon. I am getting calculations for some but for others it shows up as blank, any idea why? 
I checked the other data set the id's are definitely there and the ID's have revenue between those ranges. 

I'm not sure if I got it or not, but I tried trimming text and that seems to have fixed it so I think I am all good now. 

[mattwoldt]

Wow!

I am absolutely amazed that is incredible. It 100% works. Thank you so much! So I guess the filter function with an earlier is a way to do a dynamic sum if it seems? Just absolutely incredible. 

Thank you so much.