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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.