Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a fact table that contains TransDate and StmnUnits and another linked table that contains StmnDate. I'm trying to sum all StmnUnits that have a TransDate prior to a certain date and also the StmnDate after that same date. My formula is:
HeldHoursCalc1 = CALCULATE (
SUM( dbo_Transactions[StmnUnits] ),
dbo_Matters[MatterType] = "Hourly",
FILTER( dbo_Transactions, dbo_Transactions[TransDate] <= [As of Date] ),
FILTER( dbo_StmnLedger, dbo_StmnLedger[StmnDate] > [As of Date] )
)
This formula seems beholden to the month/year slicer and will only return values for the month selected, rather than all values prior to the [As of date].
PowerBI Result:
Desired Result:
Link to pbix file: https://drive.google.com/file/d/18TeRBVHJrgipdRPzMzXXad7EWL7igU_v/view?usp=sharing
Thank you in advance!
Solved! Go to Solution.
I solved it! If anyone comes accross this later, the solution was to use the ALLEXCEPT function on the Transactions table.
HeldHoursCalc1 =
var _max = maxx(allselected(Dates),Dates[Date])
return
CALCULATE (
SUM( dbo_Transactions[StmnUnits] ),
dbo_Matters[MatterType] = "Hourly",
FILTER( ALLEXCEPT(dbo_Transactions, dbo_Professionals[ProfName]), dbo_Transactions[TransDate] <= _max ),
FILTER( dbo_StmnLedger, dbo_StmnLedger[StmnDate] > _max )
)
I solved it! If anyone comes accross this later, the solution was to use the ALLEXCEPT function on the Transactions table.
HeldHoursCalc1 =
var _max = maxx(allselected(Dates),Dates[Date])
return
CALCULATE (
SUM( dbo_Transactions[StmnUnits] ),
dbo_Matters[MatterType] = "Hourly",
FILTER( ALLEXCEPT(dbo_Transactions, dbo_Professionals[ProfName]), dbo_Transactions[TransDate] <= _max ),
FILTER( dbo_StmnLedger, dbo_StmnLedger[StmnDate] > _max )
)
Hi @amitchandak thank you for your response. I have a date table -- the [As of date] in my post above is
@ShaelynFrench , Here the filter has come from an independent sales table
HeldHoursCalc1 =
//Date1 is an independent Date table, Date is joined with Table
var _max = maxx(allselected(Date1),Date1[Date])
return
CALCULATE (
SUM( dbo_Transactions[StmnUnits] ),
dbo_Matters[MatterType] = "Hourly",
FILTER( dbo_Transactions, dbo_Transactions[TransDate] <= _max ),
FILTER( dbo_StmnLedger, dbo_StmnLedger[StmnDate] > _max )
)
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |