Calculated column and measure depending on a date selected in the filter

[Yesus]

Hi good day, I really need your help because I am having a bad time with this scenario.

Objective: Compute the depreciation of a list of Fixed Assets, the depreciation amount will vary depending on the date selected.

I have two tables with no relation:

1. Date table (Column : Dates)

2. List of Fixed Assets ( Columns : Assets No. / Depreciation Rate / Plant No / Fixed Asset concept / Acquistion Amount / Acquisition Date.

I now know, that calculate columns/tables are computed during database processing (e.g. data refresh) and then stored in the model, they do not response to user selections on the report, but I would like to see if there is another way to solve my issue, what I am doing now is the following:

1. Measure =

   Measure = if(ISFILTERED('Cutoff Date'[Date]),MAX('Cutoff Date'[Date]),MAX('Cutoff Date'[Date]))

   Then, I am using that date selection in the measure, as basis for the new calculated columns, because I need to know the date difference in months from the acquisition date to the date selected (That date selection will be different depending in the computation need).I also tried with the selected vale formula, but it didnt work.
2. Calculated column=

Months depreciated as of last CY = switch(true(),
DATEDIFF(Entity[Acquisition date].[Date],date(year([Measure])-1,12,31),MONTH)>=Entity[Months to be depreciated (Full life)],Entity[Months to be depreciated (Full life)], 
DATEDIFF(Entity[Acquisition date].[Date],date(year([Measure])-1,12,31),MONTH)<Entity[Months to be depreciated (Full life)],
DATEDIFF(Entity[Acquisition date].[Date],date(year([Measure])-1,12,31),MONTH))

I am not receiving any error, but the time difference is not the one that I want,

What do you think?

[v-yuta-msft]

Hi Yesus,

Dynamic calculate column is not supported in power bi , you should use measure instead. Please refer to the similar case: https://community.powerbi.com/t5/Desktop/Dynamic-Column-Calculation-Based-on-Multiple-Slicers/td-p/1....

Regards,

Jimmy Tao

[Yesus]

Hi @v-yuta-msft

Thank you very much, Indeed, I did it through measures and it worked,.

Have a great day.

[Ashish_Mathur]

Hi,

I am not sure of how much i can help here but would like to try.  Share some data and show the expected result.

[Yesus]

Hi @Ashish_Mathur

Thank you very much,

I did it through measures and it worked,.

Have a great day.

[MFelix · Accepted solution]

Hi @Yesus,

You can't pass the value of a slicer, into a calculated column. You need to make this calculation on a measure and then give it context in order to have your final result.

To what I can understand from your calculated column you want to return Full life or the number of months that are remaining to the selected date, so I'm assuming you want to retrieve this on a table or something similar.

I would change this to a measure and then place it on the visual you want.

Regards,

MFelix

[Yesus]

Hi @MFelix.

Thank you very much for your support. Yes I left it for some time to think about it, and I re-make the route to go thru the measure, and it worked very well.

Have a great day!

Thank you!.

[Anonymous]

Hey! do did you make it work? I'm in a same kind of situation! please see below link.

https://community.powerbi.com/t5/Desktop/Days-Between-Table-date-and-Selected-dimdate-or-Max-dimdate...