cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Multiply a measure with factor from related table

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

``````// Basic measure - can be hidden
// 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
// 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.``````

2 REPLIES 2
Anonymous
Not applicable

``````// Basic measure - can be hidden
// 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
// 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.``````

Community Champion

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.