Summing a measure

[Anonymous]

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!

[v-cherch-msft]

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

[Anonymous]

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!

[LivioLanzo]

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

[LivioLanzo]

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:

[Anonymous]

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

[v-cherch-msft]

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

[v-cherch-msft]

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