Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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.
// 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.
Hi @PeterElbek
Try
CALCULATE(
SUM( Fact[Amount] );
FILTER(
ALL( Date );
Date[Year+week code] = TRUE()
)
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
18 | |
15 | |
13 | |
11 | |
9 |
User | Count |
---|---|
25 | |
22 | |
12 | |
11 | |
10 |