Forum Discussion

Libin7963's avatar
Libin7963
Icon for Helper II rankHelper II
1 year ago
Solved

Cumulative Total

Below Dax is used for calculating the request on hand at the beginning of the selected period. I would like to calculate the cumulative total (example :  Jan = 3, Feb = 2, March 4 then Jan = 3,  Feb ...
Need Help
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    Hi Libin7963 ,

    Once try this:

    On Hand Cumulative Total :=
    VAR CurrentDate = MAX('DimDate'[Date])
    RETURN
    SUMX(
        FILTER(
            ALL('DimDate'),
            'DimDate'[Date] <= CurrentDate
        ),
        VAR LoopDate = 'DimDate'[Date]
        RETURN
            CALCULATE(
                COUNTROWS(
                    FILTER(
                        Table1,
                        Table1[ValidDate] < LoopDate &&
                        (
                            ISBLANK(Table1[DecisionDate]) ||
                            Table1[DecisionDate] >= LoopDate
                        ) &&
                        Table1[Type] IN {
                            "01", "02", ..., "18"
                        }
                    )
                ),
                CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
            )
    )

Amar_Kumar's avatar
Amar_Kumar
Icon for Super User rankSuper User
1 year ago

On Hand Cumulative Total =
VAR CurrentMonth = MAX('DimDate'[Date])
RETURN
SUMX(
FILTER(
ALL('DimDate'[Date]),
'DimDate'[Date] <= CurrentMonth
),
CALCULATE(
COUNTX(
FILTER(
Table1,
Table1[ValidDate] < 'DimDate'[Date] &&
(ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= 'DimDate'[Date]) &&
Table1[Type] IN {
"01", "02", "03", "04", "05", "06", "07", "08",
"09", "10", "11", "12", "13", "14", "15", "16",
"17", "18"
}
),
Table1[REFVAL]
),
CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
)
)

 

Please mark my answer as complete

Libin7963's avatar
Libin7963
Icon for Helper II rankHelper II
1 year ago

Thanks for your reply, I am getting this error - A single value for column 'Date' in table 'DimDate' 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.

  • Amar_Kumar's avatar
    Amar_Kumar
    Icon for Super User rankSuper User
    1 year ago

    Libin7963.  Please try this

    FILTER(
    Table1,
    Table1[ValidDate] < SELECTEDVALUE('DimDate'[Date]) &&
    (ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= SELECTEDVALUE('DimDate'[Date])) &&
    Table1[Type] IN {
    "01", "02", "03", ..., "18"
    }
    )

    • Libin7963's avatar
      Libin7963
      Icon for Helper II rankHelper II
      1 year ago

      Thanks, I updated it as below . On Hand Cumulative Total = VAR CurrentMonth = MAX('DimDate'[Date]) RETURN SUMX( FILTER( ALL('DimDate'[Date]), 'DimDate'[Date] <= CurrentMonth ), CALCULATE( COUNTX( FILTER( Table1, Table1[ValidDate] < SELECTEDVALUE('DimDate'[Date]) && (ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= SELECTEDVALUE('DimDate'[Date])) && Table1[Type] IN { "01", "02", "03", ..., "18" } ) , Table1[REFVAL] ), CROSSFILTER('DimDate'[Date], Table1[ValidDate], None) ) ) and getting this. 

       

      • Amar_Kumar's avatar
        Amar_Kumar
        Icon for Super User rankSuper User
        1 year ago

        Libin7963 please find below the optimized version.

         

        On Hand Cumulative Total =

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

        RETURN

            SUMX(

                FILTER(

                    ALL('DimDate'[Date]),

                    'DimDate'[Date] <= CurrentDate

                ),

                CALCULATE(

                    COUNTROWS(

                        FILTER(

                            Table1,

                            Table1[ValidDate] < 'DimDate'[Date]

                            && (ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= 'DimDate'[Date])

                            && Table1[Type] IN { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10",

                                                 "11", "12", "13", "14", "15", "16", "17", "18" }

                        )

                    ),

                    CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)

                )

            )