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.
I have 2 columns data and weeknum.
I have 2 things to ask
1.I want to get sum of 8 weeks
2. Use “Relative filtering” to keep current minus 7 weeks
Let me know how can it be done
for 8 weeks im using
Solved! Go to Solution.
Hi, @Anonymous
As mentioned by @edhans ,you need to create a calendar table.
Then create measure as below:
latest_weeknum = WEEKNUM (CALCULATE( MAX ( 'Open Req'[Creation date] ),ALL('Open Req' )))
Sum of 8 weeks =
VAR latest =
MAX ( 'CTS-Starts base'[Creation date] )
RETURN
CALCULATE (
SUM ( 'Open Req'[Committed to Start - Candidate Did Not Start] ),
FILTER (
'Calendar',
WEEKNUM ( 'Calendar'[Date] )
> WEEKNUM ( latest ) - 7
&& WEEKNUM ( 'Calendar'[Date] )
< WEEKNUM ( latest ) + 1
)
)
We also need to create a measure,then apply it to visual filter pane.
visual control =
VAR weeknum =
SELECTEDVALUE ( 'Calendar'[WeekNum] )
RETURN
IF ( weeknum >= 'latest date of creation date'[latest_weeknum]- 7 && weeknum <= 'latest date of creation date'[latest_weeknum] , 1, 0 )
The result will show as below:
Best Regards,
Community Support Team _ Eason
Hi, @Anonymous
As mentioned by @edhans ,you need to create a calendar table.
Then create measure as below:
latest_weeknum = WEEKNUM (CALCULATE( MAX ( 'Open Req'[Creation date] ),ALL('Open Req' )))
Sum of 8 weeks =
VAR latest =
MAX ( 'CTS-Starts base'[Creation date] )
RETURN
CALCULATE (
SUM ( 'Open Req'[Committed to Start - Candidate Did Not Start] ),
FILTER (
'Calendar',
WEEKNUM ( 'Calendar'[Date] )
> WEEKNUM ( latest ) - 7
&& WEEKNUM ( 'Calendar'[Date] )
< WEEKNUM ( latest ) + 1
)
)
We also need to create a measure,then apply it to visual filter pane.
visual control =
VAR weeknum =
SELECTEDVALUE ( 'Calendar'[WeekNum] )
RETURN
IF ( weeknum >= 'latest date of creation date'[latest_weeknum]- 7 && weeknum <= 'latest date of creation date'[latest_weeknum] , 1, 0 )
The result will show as below:
Best Regards,
Community Support Team _ Eason
Please use this measure expression patter to get your result.
Last 8 Weeks =
VAR todaysdate =
TODAY ()
VAR enddate =
todaysdate - 49
- WEEKDAY ( todaysdate ) + 1
RETURN
CALCULATE (
[YourMeasure], //or expression
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= todaysdate
&& 'Date'[Date] >= enddate
)
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Could you explain why you added current week day : Weekday(todaysdate)+1
Your method is giving me result 0 which is wrong but it should be 9.
Power bi file -Link
That filter goes back to Oct 3rd. When I filter your date to >= that date, all the rows are 0 in that column, so the result may be correct. If you change 49 to 56, the result is 3.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You need a data table. the filter the week number in your date table in the filter pane. I have a full date table here that the weeks start on Sunday - the 2nd or 3rd line of code - Day.Sunday. Change that to be whatever you want. Then filter on the [Week of Year] column in the filter pane telling it week number 8 or less. (or 7, or whatever you need.)
Beyond that, I'd need some data to work with and a sample of expected results.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUnderstood @Anonymous. Note that my solution doesn't solve it in Power Query. It just provides the date table that way. You still solve it in DAX. You just need a good date table, and the best methods for creating a date table are either in the source system (many ERP systems have great date tables) or in Power Query which to Power BI looks like a source table.
You'd still need DAX to finalize it as DAX is great for analysis.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |