topic Re: Last YTD calculation non standard date table in DAX Commands and Tips

Last YTD calculation non standard date table

Nurry90 — Tue, 19 Jan 2021 01:47:07 GMT

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.

Re: Last YTD calculation non standard date table

amitchandak — Tue, 19 Jan 2021 02:27:00 GMT

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-Calendar-1-5-Power/ba-p/1187441
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-Power-BI-Turning/ba-p/1187482

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.

Re: Last YTD calculation non standard date table

lbendlin — Tue, 19 Jan 2021 02:27:46 GMT

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.

Re: Last YTD calculation non standard date table

Nurry90 — Wed, 20 Jan 2021 03:08:17 GMT

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.

Re: Last YTD calculation non standard date table

Nurry90 — Wed, 20 Jan 2021 03:11:06 GMT

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?

Re: Last YTD calculation non standard date table

amitchandak — Wed, 20 Jan 2021 03:19:08 GMT

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8