Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I've made a formula to calculate 12 past week based on user selection
This is working without problems
the main problem is that if user select a period (week) the other visual are not affected and they dont show the correspoing period that was filtered
i use this formula to calculate the past 12 weeks
ASN_Selected =
var selecteddate_=
IF (
ISFILTERED (Dim_Period_Filter[Pep Week2]),
MAX(Dim_Period_Filter[Date])
)
var selectedateprevious = DATE(YEAR(selecteddate_),MONTH(selecteddate_),DAY(selecteddate_)-84)
RETURN
CALCULATE(ASN_Fact[ASN %],DATESBETWEEN(ASN_Fact[Date],Convert(selectedateprevious,string),convert(selecteddate_,STRING)))
and this one is used to show the max value from the filtered period (this should not be affected by the 12 past weeks), since this value is used on a Matrix and the 12 past week is used on a Sparkline
ASN_Label =
var selecteddate_=
IF (
ISINSCOPE( Dim_Period_Filter[Pep Week2]),
MAX(Dim_Period_Filter[Date])
)
var selectedateprevious = DATE(YEAR(selecteddate_),MONTH(selecteddate_),DAY(selecteddate_))
RETURN
CALCULATE(ASN_Fact[ASN %],DATESBETWEEN(ASN_Fact[Date],Convert(selectedateprevious,string),convert(selecteddate_,STRING)))
Solved! Go to Solution.
Hi @Anonymous ,
To filter out the past 12 weeks (48 days) based on the slicer selection, you need to calculate the max and min dates for those 12 weeks.
Sample data
Here's my solution.
1.Create a calendar table.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
"YearWeek",
YEAR ( [Date] ) & " "
& FORMAT ( WEEKNUM ( [Date], 2 ), "00" )
)
Note that there is no relationship between the two tables.
2.Create two measures to get the max and min dates.
MaxDate =
VAR _select =
SELECTEDVALUE ( 'Calendar'[YearWeek] )
VAR _year =
VALUE ( LEFT ( _select, 4 ) )
VAR _week =
VALUE ( RIGHT ( _select, 2 ) )
RETURN
MINX (
FILTER (
ALL ( 'Calendar' ),
_year = YEAR ( [Date] )
&& _week = WEEKNUM ( [Date], 2 )
),
[Date]
)
MinDate = [MaxDate]-48
3.Now you can limit the dates of the main table based on the date range.
For example, create a measure to display the past 12 weeks of data for the week number selected by the slicer.
Measure = IF(MAX('Main Table'[Date])>=[MinDate]&&MAX('Main Table'[Date])<=[MaxDate],"Yes","No")
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
To filter out the past 12 weeks (48 days) based on the slicer selection, you need to calculate the max and min dates for those 12 weeks.
Sample data
Here's my solution.
1.Create a calendar table.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
"YearWeek",
YEAR ( [Date] ) & " "
& FORMAT ( WEEKNUM ( [Date], 2 ), "00" )
)
Note that there is no relationship between the two tables.
2.Create two measures to get the max and min dates.
MaxDate =
VAR _select =
SELECTEDVALUE ( 'Calendar'[YearWeek] )
VAR _year =
VALUE ( LEFT ( _select, 4 ) )
VAR _week =
VALUE ( RIGHT ( _select, 2 ) )
RETURN
MINX (
FILTER (
ALL ( 'Calendar' ),
_year = YEAR ( [Date] )
&& _week = WEEKNUM ( [Date], 2 )
),
[Date]
)
MinDate = [MaxDate]-48
3.Now you can limit the dates of the main table based on the date range.
For example, create a measure to display the past 12 weeks of data for the week number selected by the slicer.
Measure = IF(MAX('Main Table'[Date])>=[MinDate]&&MAX('Main Table'[Date])<=[MaxDate],"Yes","No")
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is the sparkline showing 12 past week (based on user selection)
Is user selects a period
my matrix (thst use the second formula to calculate ASN) is not filtering
User | Count |
---|---|
75 | |
74 | |
44 | |
31 | |
27 |
User | Count |
---|---|
99 | |
89 | |
52 | |
48 | |
46 |