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.
I was wondering if someone would help me with the following.
I have a table that has got Dates and the Amount.
What I want to return is the first and last date, with the corresponding (or associated) amount. This could potentially be the first and last values, instead of the dates.
I know that you can potentially do this with the filters, but it is taking a really long time to run.
thanks
Solved! Go to Solution.
Hi there
Thanks for that what I did was the following solution which worked for me due to having using a Date Table, which ensured that I got the last actual value and first actual value.
Sales YOY - First Date = CALCULATE([Sales],FIRSTNONBLANK('Date'[Fiscal Year],'Sales Year on Year'[TSales]))
Sales YOY - Last Date = CALCULATE([Sales],LASTNONBLANK('Date'[Fiscal Year],'Sales Year on Year'[TSales]))
Hi @GilbertQ
I suppose that your dataset contains large data which makes it spend much time to filter. But it seems that using filter to fetch the records corresponding to the first and last date is the only option currently. Based on my research, I cannot find any solution to improve the performance of running filter.
I think you can create a calculate table that contains only the first and last date with the corresponding amount like a cache so that you don't need to run the filter each time.
Best regards,
Yuliana Gu
Hi there
thanks for that I will see what i can do.
you can try with:
FirstDate-Value = CALCULATE(VALUES(Table1[Value]),FIRSTDATE(Table1[Date]))
LastDate-Value = CALCULATE(VALUES(Table1[Value]),LASTDATE(Table1[Date]))
Hi there
Thanks for that what I did was the following solution which worked for me due to having using a Date Table, which ensured that I got the last actual value and first actual value.
Sales YOY - First Date = CALCULATE([Sales],FIRSTNONBLANK('Date'[Fiscal Year],'Sales Year on Year'[TSales]))
Sales YOY - Last Date = CALCULATE([Sales],LASTNONBLANK('Date'[Fiscal Year],'Sales Year on Year'[TSales]))
Filter seems the only option as you need to get the first/last date and then use filter to get the amount for that specific date.
Thanks, I was hoping for a solution with a function!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |