Last Year To Date Using ISO Calendar

[dtg87]

Hi,

We work to an ISO Retail Week Calendar, where RetailDayofYear compares with the corresponding RetailDayofYear of last year. This differs during a 53 week year (2016,2020) when Week 53 will compare to Week1 of the Same Year. This is causing alot of headaches when attempting to create a Year to Date measure. My Date's table carries a column RetailYearLY and RetailDayofYearLY to show the date that the current date is to be compared with. This is the measure i am trying to create to get Previous YTD totals.

The idea of concatentating the LY Year and Day is so that in years where the RetailYearLY will have two values (2020, the Min Retail year and Day = 2019 and 1. However the max will be 2020 and 7.

This is getting me "function only accepts a column reference as an argument". The only thing i can think to do is convert the values to text and back to number again for the comparison? Does anyone know a way around this?

Iso PYTD :=
IF (
HASONEVALUE ( Dates[RetailYear] ),
CALCULATE (
[Total Sales £],
FILTER (
ALL ( Dates ),
CONCATENATE(Dates[RetailYear],Dates[RetailDayofYear])>= MIN(CONCATENATE(SELECTEDVALUE(Dates[RetailYearLY]),SELECTEDVALUE(Dates[RetailDayofYearLY])))
&& CONCATENATE(Dates[RetailYear],Dates[RetailDayofYear])<= MAX (CONCATENATE(SELECTEDVALUE(Dates[RetailYearLY]),SELECTEDVALUE(Dates[RetailDayofYearLY])))
)
),
BLANK ()
)

[dtg87]

If i was to replicate in SQL what i am trying to achieve it would be, i would then need to apply the logic for TD to the daily totals obtained.

select [Transaction Date], dd.RetailDayofYearLY, dd.RetailYear, SUM([Sales Value £]) as TotalTY, LY.TotalLY from SALESFACT f
left join DimDate1 dd on f.[Transaction Date]=dd.DateKey
left join (select retailyear, retaildayofyear, sum([Sales value £]) as TotalLY from V_RetailSalesFact f
left join DimDate1 dd on f.[Transaction Date]=dd.DateKey
group by retailyear, retaildayofyear) LY on (dd.retailyearly = LY.RetailYear and DD.RetailDayofYearLY=ly.RetailDayofYear)

group by [Transaction Date], LY.TotalLY, dd.RetailDayofYearLY, dd.RetailYear

[amitchandak]

@dtg87 , with a separate date table with column retaildayofyear .separate  table is must for below logic

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

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.

isoweek = weeknum([date],21)

refer if needed

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

[dtg87]

Thanks for this, but this isn't quite what i need.

I cannot do YEAR -1 to get last year in these circumstances.

Week 53 in 2020 Compares to Week 1 in 2020.

Therefore for comparing 2020 Weeks 1 - 53, they would need to filter the table on 2019 wks1 to 52 + 2020 wk1

So the MIN comparison would be 2019 01 to 2020 01