Previous Year Dax

[Anonymous]

Hi Guys
I wanted to know how can we calculate Previous fiscal year total sales.Currently my YTD dax is: TOTALYTD(SUM(Table1[Sales]),Date[Date],ALL(Date),"3/31")

Please help

[Anonymous]

To be able to carry out fiscal year calculations you have to have a fiscal calendar set up. Once you have it, you can use this formula:

[Total Sales PFY] =
// There must be a hidden integer field in the Calendar
// called FiscalYearNumber.
var __currentFiscalYear = SELECTEDVALUE( Calendar[FiscalYearNumber] )
var __totalpfy =
	calculate(
		[Total Sales],
		Calendar[FiscalYearNumber] = __fiscalYear - 1
	)
return
	__totalpfy

This is the only way to deal with fiscal calendars - you have to have a proper one in the model and write the logic yourself. There is no fiscal time-intel built-in because there are too many ways to have fiscal calendars set up whereas there is only one standard calendar.

Best

D

[Anonymous]

@AnonymousCan you help me creating that table?

[Anonymous]

Try this post
https://community.powerbi.com/t5/Desktop/DAX-Formulas-for-Fiscal-Year-Fiscal-Quarter-and-Month-Short...

and then you can read this as well
https://www.sqlbi.com/blog/marco/2018/05/22/time-intelligence-issues-in-dax-for-fiscal-years-startin...

It might be that the formula I gave you above is different for different FY calendars, so be careful.

Best
D

[Anonymous]

Try This

Hope this will work
Total Sales LY = CALCULATE( [Total Sales], DATESYTD( Dates[Date] ) )

[Anonymous]

Please, stop using TOTALYTD (or do it if you want to get into trouble). This is not recommended. Please read this article: https://www.sqlbi.com/blog/marco/2018/08/10/the-hidden-secrets-of-totalytd/

[AlexAlberga727]

@Anonymous thanks for the artilce!

[vivran22]

Hello @Anonymous 

Try following measures:

Total Orders = SUM(dtTable[Order Quantity])

YTD Orders = CALCULATE([Total Orders],DATESYTD('Calendar'[Date],"3/31"))

YTD Prev Year = CALCULATE([YTD Orders], SAMEPERIODLASTYEAR('Calendar'[Date]))

It will give you the desired output.

Cheers!
Vivek


If it helps, please mark it as a solution


Kudos would be a cherry on the top 🙂


https://www.vivran.in/

Connect on LinkedIn

[Anonymous]

@vivran22 Thanks for the solution but again my YTD is coming out correct but the Previous Year is not coming out correct.Can't figure out why

[vivran22]

@Anonymous  Can you share the sample data file?

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[AlexAlberga727]

Let's assume your current YTD measure works as you would like.

YTD = TOTALYTD(SUM(Table1[Sales]),Date[Date],ALL(Date),"3/31")

Create a New Measure - 

YTD LY = CALCULATE( YTD, DATEADD( dDATES[DATE], -1, YEAR )

[Anonymous]

@AlexAlberga727Not working its just returning me the previous calendar year .Please note that my YTD measure is working fine according to my FY year.

[Anonymous]

try sameperiodLastyear.

Thanks,

Pravin