Trying to get a running total over time with a toggle on whether or not capacity is coming online

JessSherman — Wed, 19 Jul 2023 17:32:15 GMT

So I have a table named downstream dispencing_asset_timing and it has assets with an assigned capacity coming online and going offline over time. There's a column called add or remove and when there's a 1 it means the capacity needs to be added and when it's -1 it needs to be removed. I tried writing a DAX formula for that but I keep getting errors that a single value for Date cannot be determined. Any help would be appreciated!

Running Total of Capacity =

VAR CurrentDate = 'CALENDAR'[Date]

VAR ToggleValue = 'downstream dispencing_asset_timing'[Add or Remove]

RETURN CALCULATE( SUM('CALENDAR'[Date]), FILTER( ALL('CALENDAR'), 'CALENDAR'[Date] <= CurrentDate && 'downstream dispencing_asset_timing'[Add or Remove] = 1 && 'downstream dispencing_asset_timing'[Date] <= CurrentDate ) )

Re: Trying to get a running total over time with a toggle on whether or not capacity is coming online

JessSherman — Wed, 19 Jul 2023 20:48:20 GMT

I figured it out I think:

Running Total of Capacity (Tons per Day) =

VAR CurrentDate = MAX('CALENDAR'[Date])

VAR FilteredUnits =

FILTER(

'downstream dispencing_asset_timing',

'downstream dispencing_asset_timing'[Date] <= CurrentDate &&

'downstream dispencing_asset_timing'[Add or Remove] = 1

)

RETURN

CALCULATE(

SUMX(

FilteredUnits,

DIVIDE(

RELATED('midstream fueler_assumptions'[Dispensing Capacity (kg/day)]),

1000

)

I divided by 1000 to get it in TPD instead if kg/day

topic Trying to get a running total over time with a toggle on whether or not capacity is coming online in DAX Commands and Tips

Trying to get a running total over time with a toggle on whether or not capacity is coming online

Re: Trying to get a running total over time with a toggle on whether or not capacity is coming online