YTD Calculation not working

[Anonymous]

Hello Everyone,

My Financial Year (FY) runs from December to November.

Now I am trying to create a YTD measure for sales as follows:

Sales Date Max = CALCULATE(MAX('Sales'[InvoiceDate]), All ('Sales'))

2020 Sales YTD  =

var date_2020 = DATE(2020,MONTH([Sales Date Max]),DAY([Sales Date Max]))

Return

CALCULATE([Total_Sales ($m)],

FILTER('Sales','Sales'[InvoiceDate]<=date_2020 && 'Sales'[FiscalYear]="2020"))

I have created a YTD sales table that has FY 2019, FY 2020, and FY 2021 (for the month of December) sales data. Now for FY 2021, I get only December month sales data which is correct (as I have sales data only till December for FY'21) but for FY 2019 and FY 2020, I get the whole year sales data instead of just December month sales data.

Can anyone please let me know where am I going wrong with the above measure?

Thanks.

[amitchandak]

@Anonymous , With help from date table and time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"11/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"11/30"))

Power BI December to November Financial Year Calendar: https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-December-to-November-Financial-Year-Calendar/m-p/1574446#M657

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi @amitchandak 

Thanks for looking into it and sharing the solution.

When I tried it, the first part worked, i.e. 'YTD Sales' measure worked but I am facing an issue with 'Last YTD Sales' measure. I am using my 'Invoice Date' column for dates that has duplicate date values and I think for that is creating an issue.

Error msg -> "[Last YTD Sales]: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion."

Is there a workaround where I can still use the 'Invoice Date' field to get the desired output?

Thanks for your help.

Animesh

[amitchandak]

@Anonymous , use date table, marked as date, else you will get some error or other 

I discussed these issues in details here - https://www.youtube.com/watch?v=OBf0rjpp5Hw