Multiply a measure with factor from related table

[PeterElbek]

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.

[Anonymous · Accepted solution]

// 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.

[Mariusz]

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