cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

 

Any help please? Much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Multiply a measure with factor from related table

 

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

 

View solution in original post

2 REPLIES 2
Highlighted
Super User V
Super User V

Re: Multiply a measure with factor from related table

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


 

Highlighted
Anonymous
Not applicable

Re: Multiply a measure with factor from related table

 

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

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors