Cumulative/Running Total for FYTD and FYTD YA

[Anonymous]

hi, i've been googling and digging into power bi forum and still, i've been stuck trying to calculate Total Sales between a given MONTH, in this case, the begining of the FY (fiscal year) and the latest month available in the database (up until Jun following year, when the fiscal year ends). to note, FY starts in Jul, not Jan, hence, DATESYTD will not work.

specifically, i need a sum of sales starting in July and ending in any new month when sales data comes:

- FYTD  = sales for 1 month - if only July'18 sales data available

- FYTD = sales for 2 months - if Jul'18 + Aug'18 sales data available, etc.

...

- the latest FYTD = sum of Jul-Dec'18 sales

- the last one FYTD cumulative for FY18/19 will be a sum of Jul'18-Jun'19 sales

...and then it needs to start claculating FYTD again as of Jul'19

i also need:

- FYTD YA - same logic but Year Ago e.g. Jul-Dec'17

...the last one will enable to compare fiscal-year-to-date vs same period year ago.

overall, i have data for several years, so below formula will not help me, as it calculates the entire sales since the first data point available until today.

i need is to set up a formula in a way it has a cut off point in July and runs until today = last month available. however, considering that fiscal year always ends in June, I believe, another cut off point is needed for June (still, if I have data until Dec, Jun is only in the future).

my date format is the following:

do I need to create a seperate Calendar Table and use it for my calculation? or is there any particular DAX formula that can help me to create a relevant measure?

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and build a relationship from the Date column of the All Table to the Date column of the Calendar Table.  In the Calendar Table, write these calculated column formulas to extract Year and Month.  Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").  In your visual drag Year and Mone from the Calendar Table.  Write these measures

Total sales = SUM('All'[Sales vlaue 1000 EUR])

FYTD = CALCULATE([Total Sales],DATESYTD(Calendar[Date],"30/6"))

Hope this helps.

[Anonymous]

thanks a lot!

took me a while to vizualize it, as you need to pull a correct date range, so the sum displays well and the column is not full of blanks.... still, now it works perfectly. i've just used this formula, instead of creating additional "Total Sales" measure

FYTD = CALCULATE(sum('All'[Sales Value 1000 EUR]);DATESYTD('Calendar Table'[Date Full];"30/6"))

[Ashish_Mathur]

You are welcome.