Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 calculation is showing wrong because, I assume, it does not consider the newly added colums - Region and location)
How do I ensure that when the page is being filtered, values from this measure change accordingly? I have few additional columns (Location and Region) in the dataset can be filtered by but I cannot figure out where I should add them to the statement. Are you able to help me?
Last 2 Weeks =
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
You used ALL in the formula that blocked all filters, try this:
Last 2 Weeks =
VAR _W1 =
MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] )
VAR _W2 =
CALCULATE (
MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] ),
FILTER (
ALL ( 'Terms & Hires SQL' ),
'Terms & Hires SQL'[Fiscal Week Ends] < week
)
)
VAR cal =
CALCULATE (
SUM ( 'Terms & Hires SQL'[Net Hires] ),
FILTER (
ALL ( 'Terms & Hires SQL'[Net Hires] ),
'Terms & Hires SQL'[Fiscal Week Ends] >= _W2
&& 'Terms & Hires SQL'[Fiscal Week Ends] <= _W1
)
)
RETURN
cal
https://docs.microsoft.com/en-us/dax/all-function-dax
Check this : https://www.vahiddm.com/post/weekly-time-intelligence-dax
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/
@VahidDM does not seem to work, trying to tinker with it now...
Last 2 Weeks =
VAR _W1 =
MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] )
VAR _W2 =
CALCULATE (
MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] ),
FILTER (
ALL ( 'Terms & Hires SQL' ),
'Terms & Hires SQL'[Fiscal Week Ends] < week (This only lets me enter in _W1 as the variable)
)
)
VAR cal =
CALCULATE (
SUM ( 'Terms & Hires SQL'[Net Hires] ),
FILTER (
ALL ( 'Terms & Hires SQL'[Net Hires] ),
'Terms & Hires SQL'[Fiscal Week Ends] >= _W2
&& 'Terms & Hires SQL'[Fiscal Week Ends] <= _W1
)
)
RETURN
cal
@Anonymous
My bad! try this:
Last 2 Weeks =
VAR _W1 =
MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] )
VAR _W2 =
CALCULATE (
MAXA ( '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'[Net Hires] ),
'Terms & Hires SQL'[Fiscal Week Ends] >= _W2
&& 'Terms & Hires SQL'[Fiscal Week Ends] <= _W1
)
)
RETURN
cal
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/
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
_W2
In 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |