cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slavisha84
Helper I
Helper I

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

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

slavisha84_1-1618073188093.png

 

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

slavisha84_2-1618073251626.png

 

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

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture1.pngPicture2.png

 

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.

View solution in original post

4 REPLIES 4
Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture1.pngPicture2.png

 

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.

View solution in original post

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. 

nishantchawla
Resolver IV
Resolver IV

  • 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.

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. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors