Select Last N Months from selected month , N Weeks from selected Week , N Years from selected Year

[User232431]

Hi I have dataset with Country ,Frequency & Period as a filters , now i if select one country ,any month or year or week how to display bar chart by selecting last 3 months of data from selected value

[Anonymous · Accepted solution]

Hi, @User232431 

Thanks for Lucas_LP's reply. You can try the following measure.

filter = 
VAR _country =
    SELECTEDVALUE ( 'Filter'[Country] )
VAR _frequency =
    SELECTEDVALUE ( 'Filter'[ Frequency] )
VAR _period =
    SELECTEDVALUE ( 'Filter'[Period] )
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _maxDate =
    CALCULATE ( MAX ( 'Filter'[Date] ) )
VAR _minDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Period]
                IN ALLSELECTED ( 'Filter'[Period] )
                    && 'Table'[Country]
                        IN ALLSELECTED ( 'Filter'[Country] )
                            && 'Table'[ Frequency] IN ALLSELECTED ( 'Filter'[ Frequency] )
        )
    )
VAR _minDate_before_3Month =
    CALCULATE (
        DATE ( YEAR ( _minDate ), MONTH ( _minDate ) - 3, DAY ( _minDate ) )
    )
RETURN
    IF (
        (
            _date >= _minDate_before_3Month
                && _date <= _maxDate )
                && SELECTEDVALUE ( 'Table'[Country] ) = _country
                && SELECTEDVALUE ( 'Table'[ Frequency] ) = _frequency
        ,
        1
    )

Average Value = CALCULATE(AVERAGE('Table'[Value]),FILTER('Table',[filter] = 1))

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous · Accepted solution]

Hi, @User232431 

Thanks for Lucas_LP‘s help. You can try the following dax.

Date = 
VAR _length = LEN('Table'[Period])
VAR _monthorWeek = 'Table'[ Frequency]
VAR _year = LEFT('Table'[Period],4)
VAR _month = IF(_length = 6 && _monthorWeek = "Month",RIGHT('Table'[Period],2))
VAR _week = IF(_length = 6 && _monthorWeek = "Week",RIGHT('Table'[Period],2)) * 7
VAR _yearStart = DATE(_year,1,1)
VAR _week_date = _yearStart + _week
VAR _month_date = DATE(_year,_month,1)
RETURN
IF(_length = 6 && _monthorWeek = "Month",_month_date,IF(_length = 6 && _monthorWeek = "Week",_week_date,_yearStart))

filter = 
VAR _country =
    SELECTEDVALUE ( 'Filter'[Country] )
VAR _frequency =
    SELECTEDVALUE ( 'Filter'[ Frequency] )
VAR _period =
    SELECTEDVALUE ( 'Filter'[Period] )
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _maxDate =
    CALCULATE ( MAX ( 'Filter'[Date] ) )
VAR _previousYear = 3
VAR _minDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Period]
                IN ALLSELECTED ( 'Filter'[Period] )
                    && 'Table'[Country]
                        IN ALLSELECTED ( 'Filter'[Country] )
                            && 'Table'[ Frequency] IN ALLSELECTED ( 'Filter'[ Frequency] )
        )
    )
VAR _minDate_before_3Month =
    CALCULATE (
        DATE ( YEAR ( _minDate ), MONTH ( _minDate ) - 3, DAY ( _minDate ) )
    )
VAR _minDate_before_3Year =
    CALCULATE (
        DATE ( YEAR ( _minDate ) - _previousYear , MONTH ( _minDate ), DAY ( _minDate ) )
    )
RETURN
SWITCH(
    SELECTEDVALUE('Filter'[ Frequency]),
    "Year",
    IF (
        (
            _date >= _minDate_before_3Year
                && _date <= _maxDate )
                && SELECTEDVALUE ( 'Table'[Country] ) = _country
                && SELECTEDVALUE ( 'Table'[ Frequency] ) = _frequency
        ,
        1
    ),
    IF (
        (
            _date >= _minDate_before_3Month
                && _date <= _maxDate )
                && SELECTEDVALUE ( 'Table'[Country] ) = _country
                && SELECTEDVALUE ( 'Table'[ Frequency] ) = _frequency
        ,
        1
    ))

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[User232431]

Hi @Anonymous  this working good , how can we change measure to fit if include city to country as a hirarchy slicer:

Below is measure where i have included city to country.

########################
// below measure works only if select city and showing blank if i select country as value in slicer
########################

flag =
VAR _country =
SELECTEDVALUE ( 't1'[Country] )

VAR _City =
SELECTEDVALUE ( 't1'[City] )

VAR _frequency =
SELECTEDVALUE ( 't1'[PeriodType] )
VAR _year=
SELECTEDVALUE ( 't1'[Year] )
VAR _month =
SELECTEDVALUE ( 't1'[Date_Month] )
VAR _date =
SELECTEDVALUE ( 't1_filtered'[Date] )
VAR _maxDate =
CALCULATE ( MAX ( 't1'[Date] ) )
VAR _previousYear = 11
VAR _minDate =
CALCULATE (
MIN ( 't1_filtered'[Date] ),
FILTER (
ALL ( 't1_filtered' ),
('t1_filtered'[Year],'t1_filtered'[Date_Month])
IN ALLSELECTED ( 't1'[Year] , t1[Date_Month] )
&&( 't1_filtered'[Country]
IN ALLSELECTED ( 't1'[Country] )

&& 't1_filtered'[City]
IN ALLSELECTED ( 't1'[City] ))

&& 't1_filtered'[PeriodType] IN ALLSELECTED ( 't1'[PeriodType] )
)
)
VAR _minDate_before_3Month =
CALCULATE (
DATE ( YEAR ( _minDate ), MONTH ( _minDate ) - 11, DAY ( _minDate ) )
)
VAR _minDate_before_3Year =
CALCULATE (
DATE ( YEAR ( _minDate ) - _previousYear , MONTH ( _minDate ), DAY ( _minDate ) )
)
RETURN
SWITCH(
SELECTEDVALUE('t1'[PeriodType]),
"Year",
IF (
(
_date >= _minDate_before_3Year
&& _date <= _maxDate )
&&( (SELECTEDVALUE ( 't1_filtered'[Country] ) = _country
&& SELECTEDVALUE ( 't1_filtered'[City] ) = _City))

&& SELECTEDVALUE ( 't1_filtered'[PeriodType] ) = _frequency
,
1
),
IF (
(
_date >= _minDate_before_3Month
&& _date <= _maxDate )
&&( (SELECTEDVALUE ( 't1_filtered'[Country] ) = _country
&& SELECTEDVALUE ( 't1_filtered'[City] ) = _City))

&& SELECTEDVALUE ( 't1_filtered'[PeriodType] ) = _frequency
,
1,
0
))

[User232431]

Hi , when i make relation (Many to Many & Both) for  Data and Filter table , values are not updating in graph. Request a help

[User232431]

Thanks for your reply and file ,  everything works fine except when i select month "202304" the graph showing all previous months data , can we restrict it last n months data to show instead of all previous monts, same with week and year.

Thanks

[Anonymous · Accepted solution]

Hi, @User232431 

Thanks for Lucas_LP's reply. You can try the following measure.

filter = 
VAR _country =
    SELECTEDVALUE ( 'Filter'[Country] )
VAR _frequency =
    SELECTEDVALUE ( 'Filter'[ Frequency] )
VAR _period =
    SELECTEDVALUE ( 'Filter'[Period] )
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _maxDate =
    CALCULATE ( MAX ( 'Filter'[Date] ) )
VAR _minDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Period]
                IN ALLSELECTED ( 'Filter'[Period] )
                    && 'Table'[Country]
                        IN ALLSELECTED ( 'Filter'[Country] )
                            && 'Table'[ Frequency] IN ALLSELECTED ( 'Filter'[ Frequency] )
        )
    )
VAR _minDate_before_3Month =
    CALCULATE (
        DATE ( YEAR ( _minDate ), MONTH ( _minDate ) - 3, DAY ( _minDate ) )
    )
RETURN
    IF (
        (
            _date >= _minDate_before_3Month
                && _date <= _maxDate )
                && SELECTEDVALUE ( 'Table'[Country] ) = _country
                && SELECTEDVALUE ( 'Table'[ Frequency] ) = _frequency
        ,
        1
    )

Average Value = CALCULATE(AVERAGE('Table'[Value]),FILTER('Table',[filter] = 1))

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[User232431]

Hi ,  Thanks for our reply and Power Bi Sample . The dash board works perfectly with Month and Week Slicers, Once if i select Year slicer and and value am getting below screen shot.Thanks in advance

[Anonymous]

Hi, @User232431 

The corresponding year in your table does not have a date because you are trying to calculate Value for the first three months of the year.I am unable to determine based on the year what the corresponding data is for the first three months of the year.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[User232431]

Thanks for your quick reply ,  we can add Month and Year with date function (Converting to actual Date) to year and show previours years data based on year selected.

[Anonymous]

Hi, @User232431 

You can give me an example of what you want for my reference.

Best Regards,
Yang
Community Support Team

[User232431]

[User232431]

If i select "Year" in frequency and "2024" in Period should show last N years data 

[Lucas_LP]

Hi there!

I'd reccomend setting the slicers for single select (Frequency and maybe period), just as a general recommendation.

Back to your problem, set the filter in the visual to Top N3 based in period in the filter panel (if you don't have it, go to View->Filters on the top).
As show here:

Remember to click apply filter.

Hope this is what you were looking for!

[User232431]

Thanks, My actual expectation is if select One country , Month as filter and then the data to display is last 2 months including selected month, same for Week and Year selection.

[User232431]

[Lucas_LP]

So, you dont want to have a slicer to select the period? Just get the last 3 of all the data? You can still do it as I said, just filter by top 3 periods, it will get you the 3 highest numbers, and when you filter (in slicer) by Frequency, you already filter out the ones you don't want.

Otherwise, in the same filter panel, there is an option to filter by "relative date", although I don't think it will work with your dynamic frequency.