Forum Discussion

reggierevello's avatar
reggierevello
Frequent Visitor
9 years ago
Solved

Calculate cummulative data

Hi I need help with the following calculation:

 

Based on the table below, I need to calculate the cummulative sum for each salesman based on the date as it shows in the "CummulativeRunRate" coulmn.  A start date needs to be define and the numbers of days of the fiscal month.  Also create the Delta column.

 

 

 

thanks,

Reggie

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

    reggierevello

     

    How do you want to create your fiscal calendar?

     

    I assume you already have a Calendar date in your table, then you can create a fiscal month column like:

     

    Fiscal Month = IF(MONTH('Table'[Date])-MONTH(NOW())<=0,MONTH(NOW())-MONTH('Table'[Date])+1,MONTH(NOW())-MONTH('Table'[Date])+13)

    Then generate a Fiscal Year column like:

     

     

    Fiscal Year =
IF (
    Table[Fiscal Month] < MONTH ( Table[Date] ),
    YEAR ( Table[Date] ),
    YEAR ( Table[Date] ) + 1
)

    You can still keep the Day part from Calendar Date.

     

     

    To calculate the cumulative date, you can create column like below:

     

    Cumulative Total =
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        Table,
        Table[Fiscal Year] = EARLIER ( Table[Fiscal Year] )
            && Table[Fiscal Month] = EARLIER ( Table[Fiscal Month] )
            && Table[Day] <= EARLIER ( Table[Day] )
            && Table[SalesMan] = EARLIER ( Table[SalesMan] )
    )
)

    Regards,

     

1 Reply

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

    reggierevello

     

    How do you want to create your fiscal calendar?

     

    I assume you already have a Calendar date in your table, then you can create a fiscal month column like:

     

    Fiscal Month = IF(MONTH('Table'[Date])-MONTH(NOW())<=0,MONTH(NOW())-MONTH('Table'[Date])+1,MONTH(NOW())-MONTH('Table'[Date])+13)

    Then generate a Fiscal Year column like:

     

     

    Fiscal Year =
IF (
    Table[Fiscal Month] < MONTH ( Table[Date] ),
    YEAR ( Table[Date] ),
    YEAR ( Table[Date] ) + 1
)

    You can still keep the Day part from Calendar Date.

     

     

    To calculate the cumulative date, you can create column like below:

     

    Cumulative Total =
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        Table,
        Table[Fiscal Year] = EARLIER ( Table[Fiscal Year] )
            && Table[Fiscal Month] = EARLIER ( Table[Fiscal Month] )
            && Table[Day] <= EARLIER ( Table[Day] )
            && Table[SalesMan] = EARLIER ( Table[SalesMan] )
    )
)

    Regards,