Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative Totals based on values in table

Hi Experts

 

Here is my date table. i have shown where i want the cumulative formula to start and end at for each of the 3 periods.

I have included the excel formula on how the result is to be calculated, i am trying to work out the DAX for this? You could use the rolling month column in order to split out the three reporting periods and or the index column. 

 

i can sum the cumlative sales based on the following DAX measure

Total Sales = CALCULATE(SUM('PMS_FINANCIAL_PDS'[**bleep** Sales]),
FILTER(
'PMS_FINANCIAL_PDS','PMS_FINANCIAL_PDS'[Month Start]<=EARLIER('PMS_FINANCIAL_PDS'[Month Start])))
 
BUT HOW DO I SPLIT THIS BASED ON W,X and Y

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Mariusz...

     

    thanks for the feedback, i have managed to work out a soultion..

    Total Sales =
    CALCULATE (
    SUM ( 'PMS_FINANCIAL_PDS'[**bleep** Sales] ),
    FILTER (
    'PMS_FINANCIAL_PDS',
    'PMS_FINANCIAL_PDS'[Month Start] <= EARLIER ( 'PMS_FINANCIAL_PDS'[Month Start] )
    ),
    FILTER (
    ALL ( 'PMS_FINANCIAL_PDS' ),
    [ROLL12_COMPLETE_PDS_IND]
    = EARLIER ( 'PMS_FINANCIAL_PDS'[ROLL12_COMPLETE_PDS_IND] )
    )
    )

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Mariusz...

       

      thanks for the feedback, i have managed to work out a soultion..

      Total Sales =
      CALCULATE (
      SUM ( 'PMS_FINANCIAL_PDS'[**bleep** Sales] ),
      FILTER (
      'PMS_FINANCIAL_PDS',
      'PMS_FINANCIAL_PDS'[Month Start] <= EARLIER ( 'PMS_FINANCIAL_PDS'[Month Start] )
      ),
      FILTER (
      ALL ( 'PMS_FINANCIAL_PDS' ),
      [ROLL12_COMPLETE_PDS_IND]
      = EARLIER ( 'PMS_FINANCIAL_PDS'[ROLL12_COMPLETE_PDS_IND] )
      )
      )