Time Functions

[Anonymous]

I am trying to calculate total ytd sales with the following measure

FYTD Sales $ = TOTALYTD([Extended Sales],'Daily Dates'[Date],"07/31")

The business unit I am in is currently in FY21, but when I use a slicer and select FY21, I get no return ($0 in sales).  However when I slice to FY20, I am getting the result for the current year of FY21.

I am sure I am missing something somewhere that is super simple but I cannot figure out what it is?

Can anyone assist?

[mahoneypat · Accepted solution]

Please show the formula you use to generate your FY column in the Date table.  Or check that to make sure that dates after 7/31/2020 show as FY20.

Regards,

Pat

[Anonymous]

@mahoneypat  I found my issue, and it was completely stupid.  The slicer was selecting the calendar year column rather than the fiscal year column from the date table.

Thanks for your help!

[Anonymous]

@mahoneypat , my date table is created by my data extraction team.  It has the following columns:  Date(mm/dd/yy), Month(which is the calendar month), Year(which is the calendar year), Month Name, Period (yyyymm, which is the calendar period), Fiscal Month (MM, which displays the correct fiscal month number), Fiscal Year (YYYY, which displays the correct fiscal year), and fiscal period (YYYYMM, which displays the correct fiscal year and month combination).

I have checked in the data view to ensure that the transactions there reflect the correct fiscal year as well (I have a fiscal year column for each sales transaction in the sales table as well)

However dates after 7/30/20 should calculate as FY21.