Last YTD calculation non standard date table

[Nurry90]

Hi All,

I am trying to do two things in one;

1 - Create a calculation for last year to date total ( I have used the below calculation, but it is not giving me totals)

2 - Within my calculation, I would like it to be used within different contexts/filters. I.E. I want to be able to use the same calculation in a table with Month in Year (e.g May - FY2020) and within a table with week and year (e.g wk 52 - FY2020).

I am using a non-standard date table so cannot use the built in YTD functions.

LYTD =

VAR Currentmonth = SELECTEDVALUE('Date Table'[Fiscal Month])

VAR Currentweek = SELECTEDVALUE('Date Table'[FY Week number])

VAR CurrentYear = SELECTEDVALUE('Date Table'[Fin Yr])

RETURN

CALCULATE (

[Current Sales],

FILTER(ALL('Date Table'),

'Date Table'[FY Week number] = Currentweek &&

'Date Table'[Fiscal Month] = Currentmonth &&

'Date Table'[Fin Yr] = CurrentYear - 1))

Any help would be appreciated.

[lbendlin]

Your filters would only calculate the sales for the same monthweek* of last year, but not the Last Year To Date.

* - that monthweek is a mythical being, you cannot guarantee that the same month last year had the same week numbers

If you want to calculate an actual LastYTD then what you need is to add a calculated column to your dates table that flags all days smaller than "TODAY() minus one year" as valid (true), and then add that filter to the SAMEPERIODLASTYEAR() call.

[Nurry90]

Hi @lbendlin ,

I would not have the calculation on the same table, I just want to use the same calculation in both scenarios. Is it possible to do this?

[amitchandak]

@Nurry90 , In case you have year start date you can get this easily using a calendar table

New columns

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense) //option , you can use year of year start date

Year Week = [Year]*100 +[Week]

Day of Year =datediff([Year Start date] , [Date],Day) +1

Try measures like 

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))

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

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
2.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

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.

[Nurry90]

Hi @amitchandak ,

I do not have a set start date for fiscal year. The data that I am working off is weekly data that comes in. Week 1 is not the same start date each year so i am not sure these would work.

[amitchandak]

@Nurry90 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

If you have Year (FY) and Week 

Then year week  Rank should also work 

Year Week  =[Year]*100 + [Week] 

Year Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //option , you can use year of year start date

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8