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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.