Cumulative Total Internet Sales for a given fiscal year (using AdventureWorksDW2016)

[Rumbles]

Could anyone provide guidance on creating a report to illustrate the total sales for a given fiscal year (6 April to 5th April).

I need cumlative sales for a given fiscal year plotted, along with the previous year for comparison.

Thinking of adding:

a fiscal year calculated column.

how would I subsequently do the cumulative sales amounts?

Using FactInternetSales, [OrderDate] and [SalesAmount].

Any help much appreciated!

Rumbles

[Rumbles]

[Rumbles]

Sorry to keep replying to my own post. I've got as far as to have created a Fiscal Year column, but I now need to add a cumulative sales column for the financial year. 

My hair is getting more grey my the minute here!

Guidance appreciated

Rumbles

[mattbrice]

You need to look at the TOTALYTD function.  It has a parameter that specifies a different fiscal year end date from a traditional calendar so shouldn't need to add a fiscal year column to fact table.

measure = TOTALYTD ( SUM (FactSales[Sales Amount], Calendar[Date], "04/06" )

[VivekPBI]

hi,

in my case i want to show full financial year plan ...it shouls not be restrictd for selected date.

eg: if i select sep 18 in filter then i want to show plan from apr 18 to mar 19.

Kindly suggest

[VivekPBI]

hi,

in my case i want to show full financial year plan ...it shouls not be restrictd for selected date.

eg: if i select sep 18 in filter then i want to show plan from apr 18 to mar 19.

Kindly suggest

[Rumbles]

Thanks! That really helps. How wouldI then add the previous year to date (for the same period the year before) as an additional line on the chart?

[v-sihou-msft · Accepted solution]

@Rumbles

If you use SAMEPERIODLASTYEAR() in TOTALYTD(), it will just calculate the YTD in last year. Since you need to calculate " previous year to date", you should apply a date range from the previous year date to current row date. Please refer to formula below:

Last Year up to Date =
CALCULATE (
    SUM ( 'Calendar'[Value] ),
    DATESBETWEEN (
        'Calendar'[Date],
        DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ) - 1, 4, 6 ),
        MAX ( 'Calendar'[Date] )
    )
)

Regards, 

[mattbrice]

measure previous YTD = TOTALYTD ( SUM (FactSales[Sales Amount] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) , "04/06" )

[Rumbles]

Do you mean this:

measure previous YTD= TOTALYTD (Sum (FactInternetSales[SalesAmount]),SAMEPERIODLASTYEAR(FactInternetSales[OrderDate].[Date]),"04/06")

Since there is no Calendar table for:

measure previous YTD = TOTALYTD ( SUM (FactSales[Sales Amount] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) , "04/06" )

I'm still not getting cumulative amount for the fiscal year (UK)