Skip to main content
cancel
Showing results for 
Search instead 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

Reply
dkpcr5
Helper II
Helper II

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)

dkpcr5_0-1695827579940.png

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!

1 REPLY 1
dkpcr5
Helper II
Helper II

Bump on this - any thoughts here?

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.