Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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,
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
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" )
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
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
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?
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,
measure previous YTD = TOTALYTD ( SUM (FactSales[Sales Amount] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) , "04/06" )
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |