Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Filter Problems

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)))​

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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

vstephenmsft_0-1642669502552.png

 

 

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" )
)

vstephenmsft_1-1642669587112.png

Note that there is no relationship between the two tables.

vstephenmsft_2-1642669622910.png

 

 

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

vstephenmsft_3-1642669704279.png

 

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")

vstephenmsft_4-1642669850229.png

 

 

 

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.

 

View solution in original post

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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

vstephenmsft_0-1642669502552.png

 

 

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" )
)

vstephenmsft_1-1642669587112.png

Note that there is no relationship between the two tables.

vstephenmsft_2-1642669622910.png

 

 

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

vstephenmsft_3-1642669704279.png

 

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")

vstephenmsft_4-1642669850229.png

 

 

 

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.

 

Anonymous
Not applicable

This is the sparkline showing 12 past week (based on user selection) 

EduardoCED_0-1642433642318.png

Is user selects a period 

EduardoCED_1-1642433720835.png

my matrix (thst use the second formula to calculate ASN) is not filtering

EduardoCED_3-1642433868906.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.