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,
I have a dataset on a list of assets which will need to be replaced as time goes on. Each has a different lifecycle so they will expire at different times. The table below is a sample and contains the asset, when it will expire and how much it will cost.
Assets_Medical
Main_asset_number | AssetReplacementYear | AssetValueInflation | Custom |
117364 | 2000 | $187,635 | 7/01/2000 |
117364 | 2018 | $267,989 | 7/01/2018 |
117364 | 2036 | $382,754 | 7/01/2036 |
242466 | 2005 | $2,095,140 | 7/01/2005 |
242466 | 2019 | $2,764,492 | 7/01/2019 |
242466 | 2033 | $3,647,689 | 7/01/2033 |
242466 | 2047 | $4,813,048 | 7/01/2047 |
I am trying to calculate the total value of all of the assets over time.
When pivoted against the Fiscal Year only the years which the asset is to be placed are dispayed (as shown in red).
However, I need to see the value of the asset every year, not just when the asset is replaced. I have created a measure which shows this (as shown in blue).
The issues I have is the total. I need it to be the sum of all of the assets, not the most recent change as is currently displayed. For instance 2006/2007 should be $187,635 + $2,095,140. How would I change my measure to show this?
Measure = VAR SelectedDate = LASTNONBLANK(DimDate[Date],1) VAR LastDateOfChange = CALCULATE( MAX(DimDate[Date]), FILTER( ALL(DimDate), DimDate[Date] <= SelectedDate && [Changes] = 1 )) VAR AIV = CALCULATE( SUM(Assets_Medical[AssetValueInflation]), DimDate[Date] = LastDateOfChange, ALLSELECTED(DimDate) ) RETURN AIV
Changes = IF(ISBLANK(LASTNONBLANK(Assets_Medical[AssetValueInflation],1)), 0,1)
Thanks for your help!
Hi @Anonymous
You may have a look at this post.It's better that if you could share your sample data which could reproduce your scenario and your desired output.You can also upload the pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Cherie
thanks @v-cherch-msft! I have had a look at the post and tried to implement however I am still having trouble. I have uploaded a sample dataset here with the measures I currently have.
Thanks again for your help!
Hi @Anonymous
even though you've many assets you should be getting a great compression on the dataset. besides when the DAX gets simpler it also gets faster for your users to do clicky clicky draggy droppy at a reasonable speed. It is something you can test if your report's slow
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous
you need to create an iteration with SUMX over the main_asset_number, i can-t provide the exact measure as i dont have access to your data to test it.
but something which would make your measure (and your life) much easier, would be to modify your model to something like the below, and your measure would become a simple sum
for the first asset number:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks @LivioLanzo! I agree regarding the change in the model however unfortuantely we have far too many assets for this approach to be feasible.
I have tried to iterate over the SUMX as follows however am still getting some funky results
AllItems = SUMX( VALUES(Assets_Medical[Main_asset_number]), [Measure])
I have uploaded a sample dataset and if you are able/willing to help further that would be greatly appreciated.
Many Thanks
Ben
Hi @Anonymous
You may check below measure:
Measure 2 = VAR myTable = CALCULATETABLE ( ALL ( Assets_Medical[Main_asset_number] ), ALLEXCEPT ( DimDate, DimDate[FiscalYearText] ) ) RETURN IF ( HASONEFILTER ( Assets_Medical[Main_asset_number] ), [Measure], SUMX ( myTable, [Measure] ) )
Regards,
Cherie
Hi @Anonymous
Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.
Regards,
Cherie
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |