topic Re: DAX to calculate value based on start/end date, daily price and rate in DAX Commands and Tips

DAX to calculate value based on start/end date, daily price and rate

rack201 — Wed, 09 Feb 2022 23:49:07 GMT

I'm trying to build a column that calculates the $ value of multi day 'loading' event based on a daily price and event load rate.

Something like this:

calculate the sum of ( date_n price per tonne*daily qty ) where n = load start date ... load end date

Can't get it to work.. any suggestions?

amitchandak — Thu, 10 Feb 2022 04:09:35 GMT

rack201 , In case you need a new column

New column = [Daily Qunatity] * datediff([start],[end],day)

Jihwan_Kim — Thu, 10 Feb 2022 04:17:39 GMT

Hi,

I am not sure if I understood your questions correctly, but please try below.

If the data model has the index column in the Load Table, a calculated column might seems more simple than the below.

Please check the below picture and the attached pbix file.

Load value CC =

VAR currentstart = Load[Start]

VAR currentend = Load[End]

VAR currentquantity = Load[Daily quantity]

VAR datestable =

CALENDARAUTO ()

VAR Load_datestable =

GENERATE (

FILTER (

Load,

Load[Start] = currentstart

&& Load[End] = currentend

&& Load[Daily quantity] = currentquantity

FILTER ( datestable, [Date] >= Load[Start] && [Date] <= Load[End] )

)

VAR Load_datestable_price =

ADDCOLUMNS (

Load_datestable,

"@price", LOOKUPVALUE ( 'Price'[Price per tonne], 'Price'[Date], [Date] )

)

RETURN

SUMX ( Load_datestable_price, Load[Daily quantity] * [@price] )

tamerj1 — Thu, 10 Feb 2022 04:17:46 GMT

do want this in a calculated column or you want visualize it in a matrix/pivot table? Are you using Power Bi or Excel Power Pivot?

rack201 — Tue, 15 Feb 2022 20:17:43 GMT

Fantastic- this got me to a working solution - thank you!