Filter data based on trendtype

[Anonymous]

Hello All,

I have a data like below having trendtype daily,weekly and monthly.

So I applied filter on trendtype.

If user selects trendtype as Daily then need to show only last 30 days

   If user selects   trendtype as Weekly then need to show only last 12 weeks

   If user selects    trendtype as Monthly then need to show only last 12 Months

Could any one please help how to do this

TrendType            Date               Sales

Daily	9/28/2022	155
Daily	9/29/2022	78
Daily	9/30/2022	9
Daily	10/1/2022	6
Daily	10/2/2022	5
Daily	10/3/2022	3
Daily	10/4/2022	2
Daily	10/5/2022	4
Daily	10/6/2022	9
Weekly	9/25/2022	190
Weekly	10/2/2022	180
Weekly	10/9/2022	200
Weekly	10/16/2022	260
Monthly	7/1/2022	1666
Monthly	8/1/2022	5787
Monthly	9/1/2022	3456

[v-yinliw-msft · Accepted solution]

Hi @Anonymous ,

You can try this method:

TrendType Slicer = VALUES('Trends'[TrendType])

Use these to create a new table to be a slicer.

Then in the Trend table create a measure:

Filter Flag =
VAR _day =
    DATEDIFF ( MAX ( 'Trends'[Date] ), TODAY (), DAY )
VAR _week =
    DATEDIFF ( MAX ( 'Trends'[Date] ), TODAY (), WEEK )
VAR _month =
    DATEDIFF ( MAX ( 'Trends'[Date] ), TODAY (), MONTH )
RETURN
    SWITCH (
        MAX ( 'TrendType Slicer'[TrendType] ),
        "Daily",
            IF ( _day >= 0 && _day <= 30, 1, 0 ),
        "Weekly",
            IF ( _week >= 0 && _week <= 12, 1, 0 ),
        "Monthly",
            IF ( _month >= 0 && _month <= 12, 1, 0 )
    )

Then add the measure in the filter:

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I think you need buttons and bookmarks

bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

Also field parameter can help

use Date, weekstart date , month year as axis field parameters and then change measure based on selected value.

How to switch measure I discussed here

https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f

measure will be

var _max = maxx(allselected(Date1),Date1[Date])
var _mind = _max - 30 

var _minw = _max - 72

var _minm = eomonth(_max,-12) +1  

Switch([selected Axis],

"Date", calculate(sum(Table[Value]), filter(Date, Date[Date] >= _mind && Date[Date] <= _max)) ,

"Week Start Date", calculate(sum(Table[Value]), filter(Date, Date[Date] >= _minw && Date[Date] <= _max)),

calculate(sum(Table[Value]), filter(Date, Date[Date] >= _minm && Date[Date] <= _max))

)