Market Season / Fiscal Year Reset

[Gdibbs]

Hello,

In the table below I have cumulated sales volumes by year where the cumulation resets each year.  (Cumu. Volume Count)  Then, I sum the total year value for each year.  (Year_Reset)  Then in last column I cumulate the year as a cumulative percentage so I can show the phasing development in a line chart.  (% Year_Reset)   

Here are the measure in order as listed above:

Cumu Volume Count = CALCULATE(sum(Greg_SCOPS_Excellence[Base Quantity (SKU)]), FILTER(ALLSELECTED(Greg_SCOPS_Excellence), [Calendar Day] <= MAX(Greg_SCOPS_Excellence[Calendar Day])))

Year_Reset = CALCULATE(SUM(Greg_SCOPS_Excellence[Base Quantity (SKU)]),FILTER(ALLSELECTED(Greg_SCOPS_Excellence),[Calendar Day].[Year]=MAX(Greg_SCOPS_Excellence[Calendar Day].[Year])))

Year_Reset % = CALCULATE(DIVIDE(Greg_SCOPS_Excellence[Sum of Base Quantity (SKU) YTD 2],Greg_SCOPS_Excellence[Year_Reset]))

Where I am stuck.  I want to do the same thing, but where the market / fiscal year starts in October, then runs through September.

How would that be approached?  Any help would be greatly appreciated.

[AllisonKennedy · Accepted solution]

@Gdibbs 

Well done! Sorry, the month part is my error, you need to add day in there too. In future if you can paste the measure as well as the screenshot then I can copy/paste and edit it more quickly, but see below for update with your table names (hopefully I spelled correctly).

Just confirming the Date[Calendar Day] column is a Date field? If so you can just use: 

Test 123 = CALCULATE( SUM(Greg_SCOPS_Excellence[Base Quantity (SKU)], DATESYTD(DimDate[Date], "9-30"))

[AllisonKennedy]

@Gdibbs  You really should have a date table, rather than using the built-in .[Year] hierarchy. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

In your date table, include columns for Financial Year, Financial Month, Financial Month Number. 

In your DAX, use DATESYTD https://docs.microsoft.com/en-us/dax/datesytd-function-dax

The measure below is for FY ending September: 

Cumulative Volume Count = CALCULATE( [Volume Count], DATESYTD(DimDate[Date], "9-30") )

EDIT: have updated the above measure to use "9-30" instead of just "9"

[Gdibbs]

@AllisonKennedy   

That worked perfectly as described.  Essentially it would read as " Cumulate the volume total from Oct. 1st through to Sept. 30th."

This leads to another question which I cannot make the connection to solve.  

Why can't I (maybe the right way to say it is...how do I) used the DATEYTD logic to calculate the "Year Reset" like below, but with the values being tied to the fiscal year?   I tried to do that, amongst other approaches, but it fails. 

Cumu Volume Count = CALCULATE(sum(Greg_SCOPS_Excellence[Base Quantity (SKU)]), FILTER(ALLSELECTED(Greg_SCOPS_Excellence), [Calendar Day] <= MAX(Greg_SCOPS_Excellence[Calendar Day])))

Year_Reset = CALCULATE(SUM(Greg_SCOPS_Excellence[Base Quantity (SKU)]),FILTER(ALLSELECTED(Greg_SCOPS_Excellence),[Calendar Day].[Year]=MAX(Greg_SCOPS_Excellence[Calendar Day].[Year])))

Year_Reset % = CALCULATE(DIVIDE(Greg_SCOPS_Excellence[Sum of Base Quantity (SKU) YTD 2],Greg_SCOPS_Excellence[Year_Reset]))

[AllisonKennedy · Accepted solution]

@Gdibbs  Thanks for the pasted formulas. 🙂 

To try the DATESYTD method you just need to give it the context of the FY, do you have a column in your date table for FY?

FY GrandTotal =
VAR _currentFY = MAX(DimDate[FinancialYear])
RETURN
CALCULATE( SUM(Greg_SCOPS_Excellence[Base Quantity (SKU)], DATESYTD(DimDate[Date], "9-30"), ALLSELECTED(DimDate), DimDate[FinancialYear] <= _currentFY )

[Gdibbs]

@AllisonKennedy   So, DimDate added.  (Really great!)

Here is where I am having an issue... 

When I input the measure like this I get a working result, although it is defaulting to calendar year.   Your recomended formula is under Test 123, but without the month #.  Thus, it is calculating at a calendar year level.

When I add the "9" for September I get the following:  I get an error without a description.   I did not change anything in the DimDate table.  I am lost.  Any help / guidance would be wonderful.

[AllisonKennedy · Accepted solution]

@Gdibbs 

Well done! Sorry, the month part is my error, you need to add day in there too. In future if you can paste the measure as well as the screenshot then I can copy/paste and edit it more quickly, but see below for update with your table names (hopefully I spelled correctly).

Just confirming the Date[Calendar Day] column is a Date field? If so you can just use: 

Test 123 = CALCULATE( SUM(Greg_SCOPS_Excellence[Base Quantity (SKU)], DATESYTD(DimDate[Date], "9-30"))

[Gdibbs]

Hi Allison,

First, thank you.  The dates table on your site is great.  I was able to expand on it to fit my needs.  

I am still having some issues with this.  Most likely it is an easy fix and attributable to me being a beginner.  I will try to work through it, but it I get stuck I will come back with some clarifying remarks where perhaps you can further provide guidance.  I suspect others will benefit from this...

GJD

[Gdibbs]

 Thank you.  I am very new to Power BI.  (Just a couple weeks in)  I have seen and read a bit into the dates table.  I will work on that and then try to deliver what I described.