Calculate cummulative data

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,