Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi - am having a terrible time with this one, and I feel like it should be so easy.
I need BILLED AMOUNT only if the client's first BILLED DATE is in 2020. It tells us how much new cash we are collecting this year.
I tried
but while that gives me each client's fees individually, the Total = $0.
I tried adding a measure that was MIN([BILLEDDATE]) and then doing
IF([EARLIEST FEE DATE]>=DATE(2020,1,1),SUM([BILLEDAMOUNT]),0)
but that gave me similar results.
I've tried other variations of columns and measures, but all are to no avail.
What am I doing wrong here?
Thank you!
Solved! Go to Solution.
// T is your fact table and I assume
// the model is correctly built, that is,
// it's a proper star schema with dimensions
// and facts. If not, then you have to
// transform it into a correct one.
// Main measure
[Billed Amount] = SUM( T[Billed Amount] )
// The one you want...
[Billed Amount 2020] =
var __firstDate = DATE( 2020, 1, 1 )
var __relevantCustomers =
FILTER(
// Your dimension should not contain
// a blank row. The model should not
// have referential integrity problems.
DISTINCT( Customer[CustomerID] ),
// Get only those that have their first
// transaction in 2020.
CALCULATE(
ISEMPTY( T ),
// 'Billed Date' should be a date
// table marked as such in the model.
'Billed Date'[Date] < __firstDate,
ALLEXCEPT( T, Customer )
)
)
return
CALCULATE(
[Billed Amount],
__relevantCustomers,
ALL( Customers )
)
// T is your fact table and I assume
// the model is correctly built, that is,
// it's a proper star schema with dimensions
// and facts. If not, then you have to
// transform it into a correct one.
// Main measure
[Billed Amount] = SUM( T[Billed Amount] )
// The one you want...
[Billed Amount 2020] =
var __firstDate = DATE( 2020, 1, 1 )
var __relevantCustomers =
FILTER(
// Your dimension should not contain
// a blank row. The model should not
// have referential integrity problems.
DISTINCT( Customer[CustomerID] ),
// Get only those that have their first
// transaction in 2020.
CALCULATE(
ISEMPTY( T ),
// 'Billed Date' should be a date
// table marked as such in the model.
'Billed Date'[Date] < __firstDate,
ALLEXCEPT( T, Customer )
)
)
return
CALCULATE(
[Billed Amount],
__relevantCustomers,
ALL( Customers )
)
Wow, that worked! And I don't think I ever would have figured it out on my own (well, maybe in a couple years 😁), I can't thank you enough! Solves a huge challenge for our reporting, thank you!
@Anonymous , I think it it is better you have a first BILLEDDATE as column
example
First BILLEDDATE = minx(filter(Table,[client ID] = earlier[Clinet ID]),[BILLEDDATE] )
now you filter are First BILLEDDATE last year and BILLEDDATE this year. If you can have slicer on both you can control by that
or have a formula like
new measure =
Var _min = Year(today())-1
Var _max = Year(today())
return
calculate(sum([BILLEDAMOUNT]), filter(Table ,year([First BILLEDDATE]) = _min && year([BILLEDDATE]) =_max))
or use all or allselected
new measure =
Var _min = Year(today())-1
Var _max = Year(today())
return
calculate(sum([BILLEDAMOUNT]), filter(allselected(Table) ,year([First BILLEDDATE]) = _min && year([BILLEDDATE]) =_max))
Similar approach I discussed here for cohort: https://youtu.be/Q1vPWmfI25o?t=755
Your slightly forgetting that little thing called Filter Context.
IF(MIN([BILLEDDATE])>=DATE(2020,1,1),sum([BILLEDAMOUNT]),0)
works on the current row. It works, but doesn't give you the expected result.
Instead, look at the problem from the perspective of the account, not of the individual transaction. Or, even more generic (if you also want nice totals), look at the problem from the perspective of the Totals row, ie a full collection of all accounts and their transactions.
Here's the meta approach
- Get a table variable with all account names in scope
- get the minimum billed amount for each (via MINX, or CALCULATE), and the total amount for each (via SUMX or CALCULATE)
- apply a filter to sumx only the sumx where the minx is in the current year)
That works well for the total, and while it seems to be overkill for the individual account it will still work! It won't work well for the individual transaction as you will see the same total value all over.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |