Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JessSherman
Frequent Visitor

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

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 ) )
1 REPLY 1
JessSherman
Frequent Visitor

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.