Forum Discussion
Dynamically Sum between two datasets with multiple criteria
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!
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
9 Replies
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.
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 #.
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
Hi,
Show the exact result you are expecting.
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 #.
Please explain how you arrived at 750,000 and 500,000 very clearly.
