topic Re: Multiply a measure with factor from related table in DAX Commands and Tips

Multiply a measure with factor from related table

PeterElbek — Thu, 05 Mar 2020 09:56:48 GMT

Very new to DAX and Power BI so there are some aspects I don't fully comprehend yet. Bear with me please...

I have my Fact table with

ID; Amount; Type; Transaction Date

1;200;BT;01-02-2020

2;400;BT;05-03-2020

Then of course I have my Date table, simplified:

Date; Year+week code; This week? (true/false); TodayFactor (conditional)

01-02-2020;202005;false;0

05-03-2020;202010;true;1

I've created a measure for my Fact table. The intention is to calculate the balance for the Start of The Week. However I only want the value if the week is the current week (= This week? from the Date table). Else I'd simply like 0.

BT Start of Week = CALCULATE(SUM(Fact[Amount]);FILTER(ALL(Date);Date[Year+week code]<MAX(Date[Year+week code])))

How do I proceed from here? I've tried multiplying with a factor using SUMX and RELATED, but can't get that going (and I have defined a relationship between the Fact and Date table). Also I've tried using IF-statements with no joy.

Any help please? Much appreciated.

Re: Multiply a measure with factor from related table

Mariusz — Thu, 05 Mar 2020 10:17:22 GMT

Hi PeterElbek

Try

CALCULATE( SUM( Fact[Amount] ); FILTER( ALL( Date ); Date[Year+week code] = TRUE() ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Multiply a measure with factor from related table

Anonymous — Thu, 05 Mar 2020 11:30:26 GMT

// Basic measure - can be hidden // However, hidden measures should start with _ // like: [_Balance] to mark them explicitly // they are private. I think this measure // should be private and named differently // because technically it's not a balance // unless you calculate this sum for // periods of time that start with the very // first day you have in your Date table. // Otherwise, it's just a sum of transactions // on the account, not a balance. [Balance] = SUM( Fact[Amount] ) // Your Date table should have a column // that for each day holds the 1st day // of the week to which the date belongs. // Balance @ [S]tart [o]f [Latest] [W]eek works for // any selected period of time and returns // the balance for the first day of the // most recent visible week. [Balance @ SoLW] = var __startOfWeek = MAX( 'Date'[FirstDayOfWeek] ) var __result = calculate( [Balance], 'Date'[Date] <= __startOfWeek ) return __result // Balance shown only when the week visible // is marked in the Date table as [This Week?] // C in the abbreviation stands for Current. [Balance @ SoCW] = var __isCurrentWeek = SELECTEDVALUE( 'Date'[This Week?], FALSE() ) var __balance = [Balance @ SoW] return // This will return BLANK for other weeks // but if you want to see 0 for them just // add to the whole IF expression 0. if( __isCurrentWeek, __balance ) // Of course, your Date table should contain // each and every day without gaps.