Look up Calendar Value based on Measure Conditional
Please help - been banging my head against this one for days! I have an ISSUES table and a CALENDAR table. Each ISSUE has a build cost (BC) and a cost savings (CS). My goal is to return the first calendar week in which CS >= BC (ie, when is the first cost/benefit "breakeven" month). Cost Savings (CS) is calculated on a weekly basis. To accomplish this, I've added a custom column to the CALENDAR table to index future weeks starting from TODAY, as follows: WeeknYearIndex = var weeknYearIndex = RANKX(CALCULATETABLE(VALUES(CalendarCreate[WeeknYear]), CalendarCreate[Date] >= TODAY()), CalendarCreate[WeeknYear],,ASC) RETURN IF(CalendarCreate[Date] >= TODAY(), weeknYearIndex, BLANK()) This column works as expected and, starting from TODAY, returns a numeric index for each week number into the future (so current week = 1, next week = 2, etc.). To calculate "estimated [weekly] cost savings" (CS), I'm simply multiplying estimated weekly savings rate by this index. I should add that the ISSUES and CALENDAR tables are related, one (Calendar)-to-many (Issues), on the ISSUES create date. For 90% of purposes in this data model, that's the desirable relationship. However, as you'll see below, since my goal here is to project into future dates, the measures I'm using to calculate BC and CS remove this ISSUES / CALENDAR relationship using the CROSSFILTER function. Each measure is calculated as follows: Measure 1: Estimated Build Cost CALCULATE(SUM(Issues[Story_point_estimate_11497]), CROSSFILTER(CalendarCreate[Date],Issues[CREATED],None), ALLEXCEPT(Issues, Issues[ISSUE_KEY])) Measure 2: Estimated Cost Savings RT var weeknYearIndex = CALCULATE(MIN(CalendarCreate[WeeknYearIndex]), CROSSFILTER(CalendarCreate[Date],Issues[CREATED],None), ALLEXCEPT(CalendarCreate, CalendarCreate[WeeknYear])) var savingsPerItem = CALCULATE(SUM(Issues[Estimated Weekly Cost Savings]), CROSSFILTER(CalendarCreate[Date],Issues[CREATED],None), ALLEXCEPT(Issues, Issues[ISSUE_KEY]) ) RETURN weeknYearIndex * savingsPerItem This returns my desired result, where every ISSUE_KEY (two shown for example below) has, in some future week, a rolling cost savings (CS) total, and a static build cost (BC) You'll see a "Breakeven Month" column to the right. For illustration purposes, it currently is a simple measure, as follows: IF([Estimated Weekly Cost Savings RT] >= [Estimated Build Cost Measure], 1, 0) This produces the result of showing 1 wherever CS >= BC. What I want to do instead is return the first WeeknCalendar value where, for a given ISSUE_KEY, CS >= BC. For example, beside every IDEAS-142, I'd expect to see "Week n, 2024", where CS >= $50,000. Beside every IDEAS-501, I'd expect to see "Week 43, 2023", since this is the earliest WeeknCalendar value where BC = CS ($2,000). I've tried multiple variations of the CALCULATE(MIN( function, but have gotten errors from "A function PLACEHOLDER has been used..." to "A single value for column 'WeeknCalendar' in table 'CalendarCreate' cannot be determined." Since the simple boolean measure is producing the intended result, I thought pulling the minimum WeeknCalendar value would be a breeze. Not so much! Any help would be appreciated. Thank you!
