I’m having trouble writing SUMIF calculations in DAX for dynamic windows of time. I need to flag unusual spending in different scenarios and create a visualization that shows all the flags per project.
I have two tables.
- A table that contains forecast $ amounts
- A table of contains project schedules
- I am trying to calculate the sum of total forecast % in dynamic windows of time. For example, if a project finishes construction in FY24, I need to sum the years FY25 – FY26. The next project might finish construction in FY23, so I would need to sum FY24 - FY26
Forecast
| Project # | Fiscal Year | Forecast Amount | % of Total Project Amount | Fiscal Year Start | Fiscal Year End | Fiscal Year Index |
| A200 | FY23 | 7508 | 73% | 4/1/2022 | 3/31/2023 | 1 |
| A200 | FY24 | 524 | 5% | 4/1/2023 | 3/31/2024 | 2 |
| A200 | FY25 | 1968 | 19% | 4/1/2024 | 3/31/2025 | 3 |
| A200 | FY26 | 250 | 2% | 4/1/2025 | 3/31/2026 | 4 |
| B200 | FY23 | 7699 | 6% | 4/1/2022 | 3/31/2023 | 1 |
| B200 | FY24 | 30486 | 24% | 4/1/2023 | 3/31/2024 | 2 |
| B200 | FY25 | 72565 | 58% | 4/1/2024 | 3/31/2025 | 3 |
| B200 | FY26 | 14749 | 12% | 4/1/2025 | 3/31/2026 | 4 |
Schedule
Project # | Milestone | Milestone Date | Milestone Index | Fiscal Year | Design Start Date (Added for measure) | Year before Design Complete Date (Added for measure) |
A200 | Design Complete | 1/8/2027 | 1 | FY27 | 12/31/1999 | 1/8/2026 |
A200 | Construction Start | 6/17/2027 | 2 | FY28 | 12/31/1999 | 6/17/2026 |
A200 | Construction Finish | 8/3/2028 | 3 | FY29+ | 12/31/1999 | 8/3/2027 |
B200 | Design Complete | 1/30/2024 | 1 | FY24 | 12/31/1999 | 1/30/2023 |
B200 | Construction Start | 2/17/2024 | 2 | FY24 | 12/31/1999 | 6/17/2023 |
B200 | Construction Finish | 3/27/2025 | 3 | FY25 | 12/31/1999 | 5/27/2024 |
The flags I am trying to create are for 3 scenarios:
- Sum of forecast % greater than 50% in years before design complete year
- Sum of forecast % less than 50% in years between construction start and construction finish
- Sum of forecast % greater than 10% in years after construction complete year
I was sort of able to create one flag measure. The flag measure has limitations because it only works if the visualization is filtered to one type of milestone, but the flags I need are based on several milestones. I believe a calculated column is the correct way to go about this, but I haven’t figured it out yet.
The measure is as follows:
Prior to Design % Flag=
CALCULATE (
SUM ( 'Forecast'[% of Total Project Forecast Amount] ),
FILTER (
'Forecast',
'Forecast'[Fiscal Year Start] >= MIN( 'Schedule'[Design Start Date] )
&& 'Forecast'[Fiscal Year Start] <= MAX ( 'Forecast Spend Flags'[Year before Design Complete Date])
)
)
What I am trying to achieve are results like:
Project # | Sum of % in FYs before Design Complete | Some of % between Construction Start and Construction Finish | Sum of % in FYs after Construction Finish | Flag if % before Design Complete >50% | Flag if % between Construction Start and Construction Finish < 50% | Flag if % after Construction Finish >10% | Count of Flags |
A200 | 73% | 24% | 2% | Yes | Yes | No | 2 |
B200 | 6% | 82% | 12% | No | No | Yes | 1 |
I am in over my head and would appreciate any help at all. Maybe this type of calculation is not possible in one table and I will need to create several?
