Calculate cummulative data

[reggierevello]

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

[v-sihou-msft · Accepted solution]

@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,