How to create YTD Measures in the below scenario?

[Anonymous]

Hi,

I am working with GPServer data imported into PowerBI as a table called  'AccountTransactions'. Also I have a DateTable imported from an excel file. The datetable contains fields: Date, FiscalYear, FiscalMonthNo etc. I also have a relationship created between Date Column of the datetable table and "Transaction Date" column of the AccountTransactions table. This way I can filter AccountTransactions table data by fiscal year and its working great.

The fiscal period ends 9/30 so 10/1 is the beginning (first day) of Company's new fiscal year.

I have a measure created called Suppliers Count for 2019 using the following formula: 

Supplier Dues Collected FY2019 = CALCULATE(ABS([Net Amount]),AccountTransactions[Account Number]="1-2010",'date table'[FiscalYear]=2019) + CALCULATE(ABS([Net Amount]), AccountTransactions[Account Number]="1-2010",'date table'[FiscalYear]=2019)+ CALCULATE(ABS([Net Amount]),AccountTransactions[Account Number]="1-2010",'date table'[FiscalYear]=2019)

Now based on the value of this measure, I want to calculate the following: Supplier Dues Collected YTD (year to date) for fiscal year 2019,2018, 2017 and 2016 and then have them added on a chart to see the comaprison. I haven't really worked with YTD formulas so was wondering if someone could help with the correct formulas. Also one thing I need to make sure is, the fiscal period (edning 9/30) should be accounted into the formulas so the year end date is 9/30 not 12/31.

Basically the requirement is to see the YTD Comparison of current fiscal year with last three fiscal years: 2018 , 2017, 2016.

Thanks in advance.

[GilbertQ]

Hi @Anonymous

Off the top of my head the Fiscal YTD could be done with the following measure

Fiscal YTD = TOTALYTD([Net Amount],'Date table'[Date],"09/30")

You could then put in your Fiscal Years from your Date table and it should work it out as expected.

[Anonymous]

Thanks Gilbert. Can you please clarify the last sentence about specifying fiscal years, didn’t get that part.

[GilbertQ]

Sure thing

The last part is where you can define the year end date for TOTALYTD

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

[Anonymous]

Thanks for the quick response Gilbert, I follow that but my question really is, how would i Calculate YTD for 2016, 2017 and 2018. The formula you provided would I guess give Current YTD value for FY2019. 

[v-juanli-msft]

Hi @Anonymous

Do you define the "Fiscal Year" correctly?

From your information, it seems 

date	Fiscal Year
2016/1 ~2016/9/30	FY2016
2016/10/1 ~2017/9/30	FY2017
2017/10/1 ~2018/9/30	FY2018
2018/10/1 ~2019/9/30	FY2019

to get this kind "Fiscal Year" , you could create a calcualted column in the calendar table like

Fiscal Year =
SWITCH (
 TRUE (),
 [year] = 2016&& [month] > 9, 2017,
 [year] = 2017&& [month] > 9, 2018,
 [year] = 2018&& [month] > 9, 2019,
 [year]
)

then create a measure

Measure = TOTALYTD(SUM(AccountTransactions[net amount]),'date table'[Date],"9/30")

Best Regards

Maggie

[Anonymous]

fiscal year is defined in the datetable correctly and I am not looking to show the info in the form of a table. For the requirement, i have to calculate individual values for ytd, lytd using dax. The dax you provided would work to calculate current YTD, just need a formula to calculate LYTD please.

[GilbertQ]

Hi there

You could take the measure above and do the following

LYTD = CALCULATE([measure],SAMEPERIODLASTYEAR('Date'[Date])

[GilbertQ]

Hi there

If you create a table and put in the Fiscal Year and then your Total YTD Measure, it will work it out for each Fiscal year.

Just give it a go and see if it gives you the correct values.