Cumulative Total

[bshewchu]

Get this error when I try to run a measure for cumulative total 

A single value for column 'Date' in table 'JBER 16/34 120 Day Airfield Schedule - 2023' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Can someone help me out my measure i am trying to run is 

Cumulative Total = CALCULATE(SUM('JBER 16/34 120 Day Airfield Schedule - 2023'[Manhours]),FILTER(ALL('JBER 16/34 120 Day Airfield Schedule - 2023'[Discipline]),'JBER 16/34 120 Day Airfield Schedule - 2023'[Date]<=MAX('JBER 16/34 120 Day Airfield Schedule - 2023'[Date].[Date])))

[v-yangliu-msft · Accepted solution]

Hi  @bshewchu ,

If your ALL() adds the name of the specified column instead of the name of the table, then only the specified column or measure will come out, and no other columns will appear.

Refer to:

ALL function (DAX) - DAX | Microsoft Learn

Here are the steps you can follow：

1. If the result you want is not related to [Discipline], you can modify Dax to the following:

Cumulative Total = CALCULATE(
    SUM('JBER 16/34 120 Day Airfield Schedule - 2023'[Manhours]),FILTER(ALL('JBER 16/34 120 Day Airfield Schedule - 2023'),'JBER 16/34 120 Day Airfield Schedule - 2023'[Date]<=MAX('JBER 16/34 120 Day Airfield Schedule - 2023'[Date])))

2. Result:

If the above results do not match your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly