cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Look up Calendar Value based on Measure Conditional

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!

Helper II

Bump on this - any thoughts here?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors