topic Re: Previous Year Dax in DAX Commands and Tips

Previous Year Dax

Anonymous — Mon, 17 Feb 2020 09:21:30 GMT

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

Re: Previous Year Dax

AlexAlberga727 — Mon, 17 Feb 2020 13:17:55 GMT

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 )

Re: Previous Year Dax

Anonymous — Tue, 18 Feb 2020 05:40:51 GMT

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

Re: Previous Year Dax

Anonymous — Tue, 18 Feb 2020 10:36:14 GMT

try sameperiodLastyear.

Thanks,

Pravin

Re: Previous Year Dax

vivran22 — Sat, 22 Feb 2020 03:51:37 GMT

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

Re: Previous Year Dax

Anonymous — Sat, 22 Feb 2020 11:08:41 GMT

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/

Re: Previous Year Dax

AlexAlberga727 — Mon, 24 Feb 2020 18:55:08 GMT

Anonymous thanks for the artilce!

Re: Previous Year Dax

Anonymous — Thu, 27 Feb 2020 05:41:00 GMT

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

Re: Previous Year Dax

vivran22 — Thu, 27 Feb 2020 06:02:53 GMT

Anonymous Can you share the sample data file?

Cheers!
Vivek

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

Re: Previous Year Dax

Anonymous — Thu, 27 Feb 2020 07:55:21 GMT

Try This

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

Re: Previous Year Dax

Anonymous — Thu, 27 Feb 2020 14:39:20 GMT

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

Re: Previous Year Dax

Anonymous — Fri, 28 Feb 2020 07:22:28 GMT

AnonymousCan you help me creating that table?

Re: Previous Year Dax

Anonymous — Fri, 28 Feb 2020 10:04:23 GMT

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

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-starting-in-march/

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

Best
D