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.
I am trying to find the first date where my table has information for all selected portfolios.
My table looks like this.
I'm thinking of something like this (written in english):
Min (if Calculate ( Countx(Table, PerfMonthly), filter(table, to show all rows for the selected portfolios in my slicer and the date in the current row ) = DistinctCount(Portfolio)
then return Date for that Row.
else ignore)
Solved! Go to Solution.
Thanks for all the help!
These are the formulas I used to reach my desired result.
FirstSharedDate = CALCULATE ( MAXX ( SUMMARIZE ( ALLSELECTED ( AllPerfMnthly[Portfolio] ), [Portfolio], "ABCD", MIN ( AllPerfMnthly[Date] ) ), [ABCD] ), ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Portfolio] ) ) InceptionDate = CALCULATE ( FIRSTNONBLANK ( AllPerfMnthly[Date], AllPerfMnthly[Date] ), ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Portfolio] ) )
I think i'm missing something in the request, is this what you needed?
FirstDate = FIRSTDATE(Table1[Date])
Proud to give back to the community!
Thank You!
Not exactly. If you replace your date "2/2/2017" with "4/1/2017", then you have 2 portfolios for the same date "4/1/2017". But Beta started before Charlie. Now I want to say "bring me the first date, where there is information for BOTH Beta and Charlie". That is, I want the result "4/1/2017"
I"m still not following the interaction between 'BOTH' and how that changes your request. Can you mock up some new data that clearly shows the overlaps and mock up how you want the data exported based on 'Both' requirements?
Proud to give back to the community!
Thank You!
If my Data table looks like this
Date | Fund | Performance |
31/01/2017 | ALSI | 1% |
28/02/2017 | ALSI | 2% |
31/03/2017 | ALSI | 3% |
31/03/2017 | Equity Fund | 4% |
30/04/2017 | ALSI | 5% |
30/04/2017 | Equity Fund | 6% |
I want to compare the return from inception of my "Equity Fund" against the "ALSI". ALSI starts in January but Equity Fund only starts in March.
In April, I want to show the return since inception for both of them, but only STARTING from March, because that is the first month that both funds existed. I want to start from the latest start date between all the funds I'm comparing.
So ALSI = 8.15% and Equity Fund = 10.24%.
Hi @Johnsnowlife,
I think i have solved the problem. However, i need tocheck my result. Please paste a larger data set and also show your expected result for each fund. In your dataset, please show decimal numbers so that i can compare my result with yours.
Hi @Johnsnowlife,
Try these calculated field formulas
Date of investment. This field is not really required. It is just for your information.
=CALCULATE(MIN(Returns[Date]),ALL('Calendar'))
Date of inititation for return calculation
=MAXX(SUMMARIZE(ALL(Returns),[Fund],"ABCD",MIN(Returns[Date])),[ABCD])
Returns
=CALCULATE(SUM(Returns[Performance]),DATESBETWEEN('Calendar'[Date],[Date of initation for return calculation],MAX('Calendar'[Date])))
You may download the file from here.
That hasn't worked for me.
Try this link for my full dataset with your measures.
If the Portfolios selected are "Equity Fund" and "ALSI" I need to get my begin date to be "2006/06/30".
If I select "Equity Fund" and "ALSI" and "Balanced Fund" I need to get my begin date to be "2013/10/31".
Hi @Johnsnowlife,
It is working fine. I changed the formula a bit and removed the visual level filters. The file is at the same link.
I see that it is working by itself. Please look at my latest PBIX file with your measures and my full data. You'll see if created a measure using GroupBy that gets to the same answer too.
If I use your "First Initiation Date" measure in a table by itself (bottom table) I get the correct answer with both our formulas.
But the objective of the formula is to use it in a table (top table) to get the correct start date for the performance figures for the specified month.
Currently, the StartDate formulas don't work if I've specified the latest month in the visual slicer. Or the performance measures don't work if I haven't specified the month on the visual slicer.
I'm not sure which one should be changed.
Thank you for the help so far. I hope this challenge is interesting to you...
Hi,
Why should there be 2 dates selection routes - you have dragged dates both to the filter section and are also using it as a slicer. Keep any one of them. Select a date/date range there and then see the result of my formula. If you still face problems, please let me know exactly with which selection do you face a problem.
Thanks for all the help!
These are the formulas I used to reach my desired result.
FirstSharedDate = CALCULATE ( MAXX ( SUMMARIZE ( ALLSELECTED ( AllPerfMnthly[Portfolio] ), [Portfolio], "ABCD", MIN ( AllPerfMnthly[Date] ) ), [ABCD] ), ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Portfolio] ) ) InceptionDate = CALCULATE ( FIRSTNONBLANK ( AllPerfMnthly[Date], AllPerfMnthly[Date] ), ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Portfolio] ) )
Hi,
That link does not work.
Hi @Johnsnowlife,
If my understanding is correct, you want to get the first date when both Funds appear. Please following the steps below and check if it works fine.
1. Create a measure using the formula
Total distinct Funds=CALCULATE( DISTINCTCOUNT(Table[Fund]),ALL(Fund)))
2. Create a calculated column using the formula.
Distinct Funds in each date=CALCULATE(CALCULATE(DISTINCTCOUNT(Table[Fund]),ALLEXCEPT(Table,Table[Date]))
3. Create a measure to get expected result.
First date = CALCULATE ( FIRSTDATE ( Table1[Date] ), FILTER ( Table, Table[Distinct Funds in each date] = Table[Total distinct Funds] ) )
Please feel free to ask if you have any questions.
Best Regards,
Angelia
I tried your recommendations without success.
First Measure
DistinctFunds = CALCULATE ( DISTINCTCOUNT ( AllPerfMnthly[Portfolio] ), ALL ( AllPerfMnthly[Portfolio] ) )
Second Measure and also as a column
DistinctFundsDate = CALCULATE ( CALCULATE ( DISTINCTCOUNT ( AllPerfMnthly[Portfolio] ), ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Date] ) ) )
DistinctFundsDateCol =
CALCULATE (
CALCULATE (
DISTINCTCOUNT ( AllPerfMnthly[Portfolio] ),
ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Date] )
)
)
Expected Result measure
First date = CALCULATE ( FIRSTDATE ( AllPerfMnthly[Date] ), FILTER ( AllPerfMnthly, AllPerfMnthly[DistinctFundsDateCol] = [DistinctFunds] ) )
Table of results
First Date doesn't return a result for any periods.
And I'd prefer to only use measures without calculated columns if possible.
I've created a measure with a manual entry to demonstrate my desired outcome.
For reference, ALSI TRI starts on 31/10/2005 and Equity Fund starts on 30/06/2006.
My slicer has selected only "ALSI TRI" and "Equity Fund".
MinIntersectDate = CALCULATE ( MIN ( AllPerfMnthly[Date] ), INTERSECT ( FILTER ( ALL ( AllPerfMnthly ), AllPerfMnthly[Portfolio] = "Equity Fund" ), ALL ( AllPerfMnthly ) ) )
Which gives me MinIntersectDate = 30/06/2006 in this case which is correct.
Now I need it to give me the correct answer without hard-coding any of the portfolios into the code. And to handle more than 2 portfolios selected.
Try this technique. I worked using your sample data. It seems to work
First create a New Table from Modelling Tab. This will give us Count of Portfolios against each Date
Dates&Portfolios = SUMMARIZE ( AllPerfMnthly, AllPerfMnthly[Date], "No_of_Portfolios", COUNT ( AllPerfMnthly[Portfolio] ) )
Now create a measure in your "AllperfMnthly" table as follows
CommonDate = CALCULATE ( FIRSTDATE ( 'Dates&Portfolios'[Date] ), FILTER ( 'Dates&Portfolios', 'Dates&Portfolios'[No_of_Portfolios] = MAX ( 'Dates&Portfolios'[No_of_Portfolios] ) ) )
Click here to download this file
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 |
---|---|
146 | |
85 | |
66 | |
52 | |
47 |
User | Count |
---|---|
215 | |
90 | |
83 | |
66 | |
58 |