Forum Discussion
Calculating previous values
A super user - smpa01 has helped me previously with this but I need additional help.. I am trying to calculate values for "Net Hires" the last two weeks, as you can see in my screen shot (the ca...
Does not seem to work
Anonymous
Can you share your PBIX file with me?
Share that on https://wetransfer.com/ and past the link here
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
I cannot, I am sorry, it's all coming from SQL and there is a lot of sensitve data but here is a sample of the data
Location Table SQL.Division Location Fiscal Week Begins Fiscal Week Ends Fiscal Week Num Hire_Count Term_Count Net Hires WEST 10 12/25/2021 0:00 12/31/2021 0:00 48 0 2 -2 WEST 11 1/1/2022 0:00 1/7/2022 0:00 49 0 3 -3 WEST 12 1/8/2022 0:00 1/14/2022 0:00 50 1 0 1 WEST 10 1/15/2022 0:00 1/21/2022 0:00 51 4 1 3 WEST 10 1/22/2022 0:00 1/28/2022 0:00 52 2 1 1 WEST 10 1/29/2022 0:00 2/4/2022 0:00 1 0 1 -1 WEST 10 2/5/2022 0:00 2/11/2022 0:00 2 1 0 1 WEST 1050 12/25/2021 0:00 12/31/2021 0:00 48 1 2 -1 WEST 50 1/1/2022 0:00 1/7/2022 0:00 49 0 2 -2 Hi Anonymous
Try this:
Last 2 Weeks = VAR _W1 = MAX ( 'Terms & Hires SQL'[Fiscal Week Ends] ) VAR _W2 = CALCULATE ( MAX ( 'Terms & Hires SQL'[Fiscal Week Ends] ), FILTER ( ALL ( 'Terms & Hires SQL' ), 'Terms & Hires SQL'[Fiscal Week Ends] < _W1 ) ) VAR cal = CALCULATE ( SUM ( 'Terms & Hires SQL'[Net Hires] ), FILTER ( ALL ( 'Terms & Hires SQL'), 'Terms & Hires SQL'[Fiscal Week Ends] >= _W2 && 'Terms & Hires SQL'[Fiscal Week Ends] <= _W1 ) ) RETURN _W2In your sample Data the result is zero (11-Feb net Hire is 1 and 4-Feb it's -1)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/It's a date and if created as a new column and formatted as a number the values are the same