How to calculate YTD based on fiscal week and day of the week?

[slavisha84]

Hi, 
I am trying to calculate the percent difference between this year's YTD and last year's YTD based on fiscal week and day of the week. 
For example, if we are in fiscal week 15 and today is Thursday, the YTD for this year will be from the beginning of this year until Fiscal Week 15, day Thursday. For the last year, YTD will be From the beginning of the last year until Thursday of the fiscal week 15 of last year. 
I created two tables in which I have a date, day name, fiscal week...
Fiscal Calendar Table

And RevenueByDate Table:

So I think the logic would be something like this:

Create variable to store a current week

Create variable to store day of the current week
Summarize data from the beginning of 2021 until the current fiscal week and current day. 

Then do the same for 2020 and then calculate the percent difference. 

How do i do this in DAX?

[CTozzi]

@slavisha84 try this:

1 - Create measure with Total Revenue:

Revenue Amount = SUM(RevenuebyDate[Revenue])

2 - Compute the Fiscal Day Number:

FiscalDayNumber = (FiscalCalendar1[FiscalWeek]-1) * 7 + WEEKDAY(FiscalCalendar1[Date],2)

3 - Calculate the Revenue Same Period Last Year
Revenue SPLY =

IF (
HASONEVALUE ( FiscalCalendar[FiscalYear] ),
CALCULATE (
[Revenue Amount],
ALL ( 'FiscalCalendar' ),
VALUES ( FiscalCalendar[FiscalDayNumber] ),
FiscalCalendar[FiscalYear] = VALUES ( FiscalCalendar[FiscalYear]) - 1 ))

4 - Calculate the Difference vs Lat Year
% Dif Revenue LY = RevenuebyDate[Revenue Amount] / RevenuebyDate[Revenue SPLY] -1

I hope that works.

Should look like this:

[CTozzi · Accepted solution]

@slavisha84 , I realized that was not exactly what you have asked, so here it goes the correction:

1 - Create measure with Total Revenue:

Revenue Amount = SUM(RevenuebyDate[Revenue])

2 - Compute the Fiscal Day Number:

FiscalDayNumber = (FiscalCalendar[FiscalWeek]-1) * 7 + WEEKDAY(FiscalCalendar[Date],2)

3 - Calculate the Revenue Year to Date:
Revenue YTD = IF (
HASONEVALUE ( FiscalCalendar[FiscalYear] ),
CALCULATE(
[Revenue Amount],
ALL (FiscalCalendar),
FILTER ( ALL ( FiscalCalendar[Date] ), FiscalCalendar[Date] <= MAX ( FiscalCalendar[Date] ) ),
VALUES ( FiscalCalendar[FiscalYear] )
)
)

4 - Calculate the Revenue PREVIOUS Year to Date:
Revenue PYTD =
IF (
HASONEVALUE ( FiscalCalendar[FiscalYear]),
CALCULATE (
RevenuebyDate[Revenue YTD],
FILTER (
ALL ( FiscalCalendar),
FiscalCalendar[FiscalYear] = VALUES ( FiscalCalendar[FiscalYear] ) - 1
&& CONTAINS(
VALUES ( FiscalCalendar[FiscalDayNumber] ),
FiscalCalendar[FiscalDayNumber],
FiscalCalendar[FiscalDayNumber] )
)
),
BLANK ()
)

5 - Calculate the Difference YOY %
% YOY = IF(RevenuebyDate[Revenue PYTD] <> 0, RevenuebyDate[Revenue YTD] / RevenuebyDate[Revenue PYTD] -1, BLANK() )

I hope that works.

[Jihwan_Kim]

Hi, @slavisha84 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

I tried to create the same or similar calendar table as yours.

I am not very familiar with dealing with weekly basis year comparison, but I think your logic is correct.

Please suggest if I missed something.

Qty WYTD previous year =
CALCULATE (
[Qty WYTD],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[FiscalWeek] = MAX ( 'Calendar'[FiscalWeek] )
&& 'Calendar'[Day Name] = MAX ( 'Calendar'[Day Name] )
&& 'Calendar'[FiscalYear]
= MAX ( 'Calendar'[FiscalYear] ) - 1
)
)

https://www.dropbox.com/s/yg7raxsed6bpoy5/slavisha.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[slavisha84]

@Jihwan_Kim Thank you Kim, this is very close to what I have. 
Here is the folder where I have a sample of data:
https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=sZQshq
I forgot to include that in the original post. 

So the goal is to calculate quantity from the beginning of the last year to the same week of this year and the same day of this week. So since we are in the 15th fiscal week and it is Thursday, I need to calculate last Year YTD as sum of revenue between fiscal week1 last year and fiscal week 15th fiscal week last year but only untill Thursday of that 15th week. 

[amitchandak]

@slavisha84 , in your date/week table, create FY and FY Week

then try measures like examples

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

How to create FY week https://youtu.be/euIC0dgGTNM

[slavisha84]

I don't understand the logic with FY Week....

I already have Fiscal Weeks and Day. 
Why can't we use that? What if i use days in terms of 1,2,3,4,5,6,7 instead of using their names?

Is there any other solution for this?