Filter dates based on slicer in power bi

[Anonymous]

Hello All,

I have table where I am having daily data like date,sales,profit and discount.

My requirement is I need to show a slicer which have values like daily,weekly,monthly and Quarterly.

If the user selects daily I need to show yesterday sales,profit and discount.(27th March)

If the user selects Monthly I need to show last month  data.(February 2023)

If the user selects Weekly I need to show last  Weekdata. (Mar 19 to Mar 25)

If the user selects Quarterly I need to show last  qtr data. (oct - Dec 2022)

So I created a table in power bi with these values and showing as slicer.

In the report I have lot of measures .So I cant calculate above logic for each measure.

So I need a dax which can filter my page for above requirement

I wrote the below one but its a static one for now.

Please help on this.

Filter Date Snapshot =

var selected_value = SELECTEDVALUE(TrendType1[TrendType])

var last_Day = if(CALCULATE(MAX(agg_paths[path_metrics.Date]),FILTER(agg_paths,agg_paths[path_metrics.Date]>= TODAY()-1 && agg_paths[path_metrics.Date] <= TODAY())),1,0)

var last_week = if(CALCULATE(MAX(agg_paths[path_metrics.Date]),FILTER(agg_paths,agg_paths[path_metrics.Date]>= TODAY()-8 && agg_paths[path_metrics.Date]<= TODAY())),1,0)

var last_Month = if(CALCULATE(MAX(agg_paths[path_metrics.Date]),FILTER(agg_paths,agg_paths[path_metrics.Date] >= TODAY()-54 && agg_paths[path_metrics.Date]<= TODAY())),1,0)

var last_qtr = if(CALCULATE(MAX(agg_paths[path_metrics.Date]),FILTER(agg_paths,agg_paths[path_metrics.Date]  >= TODAY()-85 && agg_paths[path_metrics.Date]<= TODAY())),1,0)

return IF(

    selected_value = "Daily",

    last_Day,

    IF(

       selected_value ="Weekly",

        last_week,

    IF(

       selected_value ="Monthly",

        last_Month,

            IF(

       selected_value ="Quarterly",

        last_qtr

))))

Not sure how to write the dax to calulate these.

[halfglassdarkly]

You may run into problems e.g subtracting 1 from current month if your current month is Jan. it would be safer to use something like EODATE to return the last day of the previous month then filter on 

year(EODate([Date]-1)) && month(EODate([Date]-1))

[SamInogic]

Hi,

As per our understandings you are looking for a Filter that can filter your table data on different date selectors like Lastweel, Lastmonth and so on,

You can achieve this by below simple steps,

This is Sample Table 

Create a Weeknum and Quarter Column 

WeekNumber = WEEKNUM('Sample table'[Date])

Quarter = 'Sample table'[Date].[QuarterNo]

Then Using them create a Resultant column 

Needed slicer =

var yesterDay =

IF('Sample table'[Date].[Day]=DAY(NOW())-1,"Yesterdays

 sales",BLANK())

var lastMonth =

IF('Sample table'[Date].[MonthNo]=MONTH(NOW())-1,"Last

 Month",BLANK())

var lastWeek =

IF('Sample table'[WeekNumber]=MAX('Sample table'[WeekNumber])-1,"Last

 Week",BLANK())

var lastQuarter =

IF(MAX('Sample table'[Quarter])-1 =

'Sample table'[Quarter],"Last Quarter",BLANK())

return IF(yesterDay<>blank(),yesterDay,

       IF(lastMonth<>BLANK(),lastMonth,

       IF(lastWeek<>BLANK(),lastWeek,

       IF(lastQuarter<>BLANK(),lastQuarter))))

Use this column in Slicer 

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[Anonymous]

Hi @SamInogic ,

Thanks for your help.

Could you please make this formula so that I can use as a filter .

I mean I can keep in filters select 1 so that my report can change like my below frmula.

Filter Date Snapshot =

var selected_value = SELECTEDVALUE(TrendType1[TrendType])

var last_Day = if(CALCULATE(MAX(agg_paths[path_metrics.Date]),FILTER(agg_paths,agg_paths[path_metrics.Date]>= TODAY()-1 && agg_paths[path_metrics.Date] <= TODAY())),1,0)

var last_week = if(CALCULATE(MAX(agg_paths[path_metrics.Date]),FILTER(agg_paths,agg_paths[path_metrics.Date]>= TODAY()-8 && agg_paths[path_metrics.Date]<= TODAY())),1,0)

var last_Month = if(CALCULATE(MAX(agg_paths[path_metrics.Date]),FILTER(agg_paths,agg_paths[path_metrics.Date] >= TODAY()-54 && agg_paths[path_metrics.Date]<= TODAY())),1,0)

var last_qtr = if(CALCULATE(MAX(agg_paths[path_metrics.Date]),FILTER(agg_paths,agg_paths[path_metrics.Date]  >= TODAY()-85 && agg_paths[path_metrics.Date]<= TODAY())),1,0)

return IF(

    selected_value = "Daily",

    last_Day,

    IF(

       selected_value ="Weekly",

        last_week,

    IF(

       selected_value ="Monthly",

        last_Month,

            IF(

       selected_value ="Quarterly",

        last_qtr

))))

[halfglassdarkly]

I'd suggest you create a date dimension in PowerQuery or DAX and add calculated columns to indicate current and previous period for week, month, quarter. e.g using DATEDIFF(TODAY(),date dim'[date],WEEK) to get the negative offset in weeks between today's date and each date in your date dimension.

You'll still need to include code in your measure to switch between calculations with different date filters depending on your slicer value but it should simplify your code.

Also you should check out SWITCH() instead of using nested if statements 🙂