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

[rack201]

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 · Accepted solution]

@rack201 , In case you need a new column

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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[tamerj1]

Hi @rack201 

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?

[Jihwan_Kim · Accepted solution]

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] )

[rack201]

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

[amitchandak · Accepted solution]

@rack201 , In case you need a new column

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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...