Sales YTD over Multiple Fiscal Years

Power BI
1

Hi there,

Having some trouble doing something that would seem quite simple (and I wouldn’t be surprised if the solution is fairly simple as well), but hoping I could get some help here.

I’m trying to show a matrix that has Sales YTD in the row, and Fiscal Years in the column.

image

The Sales YTD measure is as follows:

Sales YTD = CALCULATE([Sales YTD],DATESYTD('4a - Calendar Table'[Date],"31/3"))

A few notes:

  • My fiscal year runs from April 1 - March 31.
  • Assume the current date is January 27, 2019.
  • I’m using the calendar code from Enterprise DNA training

The FY19 column shows up fine, as the dollars represent sales from April 1, 2018 - January 27, 2019. But for all prior fiscal years (e.g. FY 18), it’s showing total dollars for the entire fiscal year from April 1 - March 31, rather than from the period of April 1 - January 27 in each respective fiscal year so it’s no longer an apples to apples comparison.

How do I fix it so that each fiscal year column shows the same period (e.g. April 1 - January 27)?

(my original workaround was to create a YTD measure for each fiscal year, but it’s harder to keep clean formatting)

Thanks in advance for your help!

Kevin

2

Hi @keha,

Let me see if I undertood correctly:

What you want is to compare exactly the same time periods from each fiscal year, is this correct?

For this I would recommend doing an slicer on the months, years, etc. that you want to compare.

From there you can use measures with calculate and use SAMEPERIODLASTYEAR function.

I would recommend going through the course Mastering DAX Calculation → Time intelligence functions section. I will put the link to the first video in here:

http://portal.enterprisedna.co/courses/108877/lectures/2000648

Let me know if this helps you.

Best Regards,

Jorge Galindo