Forum Discussion

Glaeran's avatar
Glaeran
Frequent Visitor
9 years ago
Solved

Cumulative Total with two columns

Hello Everyone,

 

I'm dealing with following scenario.

We have table with following structure:

 

 

What I would like to do is:

Calculate accumulative total for POS_AMT measure based on WEEK_END_DATE.

I did that using following formula:

POS_AMT_CUMULATIVE = 
CALCULATE(
	SUM(ARD_POS_FRCST_WKLY[POS_AMT]);
	FILTER(
		ALLEXCEPT(ARD_POS_FRCST_WKLY; ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME];ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]);
		ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX(ARD_POS_FRCST_WKLY[WEEK_END_DATE])
	)
)

However, at certain point, POS_AMT values for few weeks are Blanks, with just FORECAST_AMT column being filed.

 

 

What I would want to achieve is -> For WEEK_END_DATE values that have FORECAST_AMT, but doesn't have POS_AMT use FORECAST_AMT insead of POS_AMT in cumulative calculation.

 

The other way would be to calculate cumulative POS_AMT for only those weeks that have such values and later on calculate another measure with FORECAST_AMT, but only for those weeks that doesn't have POS_AMT.

 

Hope it's clear enough... :)

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Glaeran

     

    Hi,

    This formula can mark the first week that starts using FORECAST_AMT. Maybe you need to make some changes. It's used as calculated column. Hope this would help.

    Column =
IF (
    ARD_POS_FRCST_WKLY[WEEK_END_DATE]
        = CALCULATE (
            MIN ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ),
            FILTER ( ARD_POS_FRCST_WKLY, ARD_POS_FRCST_WKLY[POS_AMT] = 0 )
        ),
    1,
    0
)

     

    Best Regards!

    Dale

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Glaeran

     

    Hi, 

    You just need to change SUM into SUMX. Try this one below.

    POS_AMT_CUMULATIVE =

    CALCULATE (

        SUMX (

            ARD_POS_FRCST_WKLY,

            IF (

                ARD_POS_FRCST_WKLY[POS_AMT] = 0,

                ARD_POS_FRCST_WKLY[FORECAST_AMT],

                ARD_POS_FRCST_WKLY[POS_AMT]

            )

        ),

        FILTER (

            ALLEXCEPT (

                ARD_POS_FRCST_WKLY,

                ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME],

                ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]

            ),

            ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )

        )

    )

    • Glaeran's avatar
      Glaeran
      Frequent Visitor
      9 years ago

      Thank you!

       

      That actually is working almost as expected.

      "Almost", because our "ideal" scenario, would be to have those values that are using [FORECAST_AMT} column to be colored separately. Is it doable though?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Glaeran

         

        Hi,

         

        We can’t color them separately due to we accumulate the total. If the first  month uses FROCAST_AMT, we can say the last month uses it too. If we want to find out the current month using it or not, we can add a calculated column, and then add it to legend. This doesn’t work with area chart. You can have a look at the image below. The black column means this column uses FROCAST_AMT. Is this what you want?

         

        UsingFrocast =

        CALCULATE (

            IF ( MIN ( ARD_POS_FRCST_WKLY[POS_AMT] ) = 0, 1, 0 ),

            FILTER (

                ARD_POS_FRCST_WKLY,

                MONTH ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )

                    = MONTH ( EARLIER ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ) )

            )

        )


         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best regards

        Dale