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

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)

        )

    )

 

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

Thanks, I am getting this error  -

Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.

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

    Replace sunx section in the code, try passing 

    SUMX(full table to filter.

        FILTER(

            ALL('DimDate'), 

            'DimDate'[Date] <= CurrentDate

        ),

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

      modified as below and getting same error - 

      n Hand Cumulative Total = VAR CurrentDate = MAX('DimDate'[Date]) RETURN SUMX(Table1,FILTER(ALL('DimDate'[Date]),'DimDate'[Date]....
       
      • Amar_Kumar's avatar
        Amar_Kumar
        Icon for Super User rankSuper User
        1 year ago

        I tried and didnot got error with this modification, 

         

        On Hand Cumulative Total =

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

        RETURN

            SUMX(

                FILTER(

                    ALL('DimDate'),

                    'DimDate'[Date] <= CurrentDate

                ),

                CALCULATE(

                    COUNTROWS(

                        FILTER(

                            Table1,

                            Table1[ValidDate] < EARLIER('DimDate'[Date]) &&

                            (

                                ISBLANK(Table1[DecisionDate]) ||

                                Table1[DecisionDate] >= EARLIER('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)

                )

            )