Forum Discussion
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.
// 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.
2 Replies
- Mariusz
Community Champion
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
// 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.
