Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to get a rolling measure and I am struggling to get the desired results. Below is an example of the data I am trying to summarize. 'Start' has a N:1 relationship with the Date table. My goal is to sum "Column" as long as the month is within the periods.
Start | End | Column |
3/9/2020 | 6/17/2020 | 100 |
3/16/2020 | 6/30/2020 | 40 |
3/19/2020 | 4/3/2020 | 60 |
4/3/2020 | 4/10/2020 | 20 |
4/6/2020 | 4/17/2020 | 80 |
4/6/2020 | 4/17/2020 | 80 |
4/6/2020 | 5/22/2020 | 25 |
4/16/2020 | 4/21/2020 | 0 |
4/21/2020 | 5/21/2020 | 60 |
5/11/2020 | 5/15/2020 | 15 |
6/4/2020 | 6/8/2020 | 5 |
7/1/2020 | 8/31/2020 | 80 |
An example of the output would be grouped on the month in the date table:
March-20 | 200 |
April-20 | 465 |
May-20 | 240 |
June-20 | 145 |
July-20 | 80 |
I can get the column to sum in a cumulative fashion, with the below DAX, but I cannot seem to use the inverse with "End" to drop the values expired, because for example, March has some values that should carry until June, while others should be dropped in April, so it seems to drop one or the other.
Measure =
CALCULATE(
SUM('table'[Column]),
FILTER(
ALLSELECTED(Dates[Date]),
ISONORAFTER(Dates[Date], MAX('table'[Start]),DESC)
))
I could use some direction on a better approach.
Solved! Go to Solution.
Please try this expression instead. This got your desired result in a table visual with the YearMonth column from the Date table.
Total Between =
VAR mindate =
MIN ( 'Date'[Date] )
VAR maxdate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
SUM ( 'Table'[Column] ),
ALL ( 'Table' ),
'Table'[Start] <= maxdate,
'Table'[End] >= mindate
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression instead. This got your desired result in a table visual with the YearMonth column from the Date table.
Total Between =
VAR mindate =
MIN ( 'Date'[Date] )
VAR maxdate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
SUM ( 'Table'[Column] ),
ALL ( 'Table' ),
'Table'[Start] <= maxdate,
'Table'[End] >= mindate
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |