How to calculate % Difference for This YTD vs Last Year YTD based on current fiscal week.

[slavisha84]

Hello, 
I have two tables, one which is Fiscal Calendar that looks like this:

And second table is Revenue by Fiscal Week for each year that looks like this:

What I need to do is create the measure and store it on the card. 
The measure should display the number that is defined as the % Difference between YTD This Year vs YTD last Year based on the fiscal weeks. 
So if we are right now in the 14th fiscal year, then this number would be the sum of revenue from 1st to 14th fiscal week of 2021- sum o of the 1st-14th week of 2020) and divided it by sum o of 1st-14th week of 2020. 

So I think within measure, first, I have to create a variable to tell me in which fiscal week I am.  To do that I would use the FiscalCalendar table with fiscal date and fiscal week columns. Once I return the current fiscal week from there I would set that as the max value to which I will run the sum. I know how to do this in excel but I can't figure it out here in DAX. 

Can someone please help me?

I am attaching the link to the shared folder so you can see the tables within Excel and Also the Pbix file. 

https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=sZQshq

[Jihwan_Kim · Accepted solution]

Hi, @slavisha84 

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

I used the below two tables and created a relationship.

all measures are in the sample pbix file.

https://www.dropbox.com/s/yk60lce8i1i6qxw/Sample%20%281%29.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.

[slavisha84]

Oh sorry, I think you used the wrong table. You need to use the table RevenueByFiscalWeek because that is the format I am using. I cant use the RevenueByDate. You have to completely ignore the RevenueByDate table. I should have removed it. 

[Anonymous]

• Hi @slavisha84 Create a variable first, calculate(max(weekno.),sum(2021)<>blank)
• Then calculate(sum(2020),filter(all(fiscalweek),fiscalweek=var created earlier
• Then return divide(sum(2021)-above measure,2020 measure,0)

Mark this solution as accepted.

[slavisha84]

I dont think this is going to work. 
I think my variable needs to be based on the current date and then look up which fiscal week the current fiscal date falls into. Then from there, I need to use retrieved fiscal week against fiscal week in another table that contains the revenue.