Filtering data for Monthly and yesterday on the visuals based on button selection

[mazeed92]

Hi,
I have developed the report like below. Now I want to keep two button on this page, 1. Month 2.Yesterday. Based on the selection on the button, data should be shown on the visuals on the entire page.

1. Month button - Month to Date

2. Yesterday button - To show the yesterday data (Availble previous date from the MaxDate)

Please help on this

Thanks

Abdul

[pankajnamekar25 · Accepted solution]

Hello @mazeed92 

Try this

 implement a toggle between "Month to Date" and "Yesterday" in Power BI using buttons, you can follow this method entirely within one page using DAX and a disconnected table.

First, create a disconnected table to act as your toggle selector. Go to the Modeling tab and create a new table with this DAX:

DateFilter =

DATATABLE(

    "Period", STRING,

    {

        {"Month"},

        {"Yesterday"}

    }

)

This table will not be related to any other table in your model. Add this DateFilter[Period] column to a slicer visual, format it horizontally, and style it like buttons.

Now, create a measure that adjusts based on the selected period. Here’s an example for sales or amount collected:

SelectedAmount =

VAR SelectedPeriod = SELECTEDVALUE(DateFilter[Period])

VAR MaxDate = MAX('Date'[Date])

RETURN

    SWITCH(

        SelectedPeriod,

        "Month", CALCULATE(SUM('Sales'[Amount]), DATESMTD('Date'[Date])),

        "Yesterday", CALCULATE(SUM('Sales'[Amount]), 'Date'[Date] = MaxDate - 1)

    )

You can replicate similar logic for other KPIs. For example, if you have a KPI for Total Customers:

SelectedTotalCustomers =

VAR SelectedPeriod = SELECTEDVALUE(DateFilter[Period])

VAR MaxDate = MAX('Date'[Date])

RETURN

    SWITCH(

        SelectedPeriod,

        "Month", CALCULATE(SUM('CustomerData'[TotalCustomers]), DATESMTD('Date'[Date])),

        "Yesterday", CALCULATE(SUM('CustomerData'[TotalCustomers]), 'Date'[Date] = MaxDate - 1)

    )

Make sure your 'Date' table is marked as a date table and is properly related to your fact tables like 'Sales' or 'CustomerData'.

Once these measures are created, replace your existing measures in the visuals with these dynamic ones. Now, when the user selects either "Month" or "Yesterday" from the slicer, the entire page will update accordingly, showing the correct data context.

Thanks 

Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[pankajnamekar25 · Accepted solution]

Hello @mazeed92 

Try this

 implement a toggle between "Month to Date" and "Yesterday" in Power BI using buttons, you can follow this method entirely within one page using DAX and a disconnected table.

First, create a disconnected table to act as your toggle selector. Go to the Modeling tab and create a new table with this DAX:

DateFilter =

DATATABLE(

    "Period", STRING,

    {

        {"Month"},

        {"Yesterday"}

    }

)

This table will not be related to any other table in your model. Add this DateFilter[Period] column to a slicer visual, format it horizontally, and style it like buttons.

Now, create a measure that adjusts based on the selected period. Here’s an example for sales or amount collected:

SelectedAmount =

VAR SelectedPeriod = SELECTEDVALUE(DateFilter[Period])

VAR MaxDate = MAX('Date'[Date])

RETURN

    SWITCH(

        SelectedPeriod,

        "Month", CALCULATE(SUM('Sales'[Amount]), DATESMTD('Date'[Date])),

        "Yesterday", CALCULATE(SUM('Sales'[Amount]), 'Date'[Date] = MaxDate - 1)

    )

You can replicate similar logic for other KPIs. For example, if you have a KPI for Total Customers:

SelectedTotalCustomers =

VAR SelectedPeriod = SELECTEDVALUE(DateFilter[Period])

VAR MaxDate = MAX('Date'[Date])

RETURN

    SWITCH(

        SelectedPeriod,

        "Month", CALCULATE(SUM('CustomerData'[TotalCustomers]), DATESMTD('Date'[Date])),

        "Yesterday", CALCULATE(SUM('CustomerData'[TotalCustomers]), 'Date'[Date] = MaxDate - 1)

    )

Make sure your 'Date' table is marked as a date table and is properly related to your fact tables like 'Sales' or 'CustomerData'.

Once these measures are created, replace your existing measures in the visuals with these dynamic ones. Now, when the user selects either "Month" or "Yesterday" from the slicer, the entire page will update accordingly, showing the correct data context.

Thanks 

Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[mazeed92]

Thanks,I am connecting a DataSource via DirectQuery. How about the charts like Pie Charts, BarCharts used on the Page ?

[v-tejrama · Accepted solution]

Hi @mazeed92,

Thanks for reaching out to the Microsoft fabric community forum.

Yes, this method works with DirectQuery as well. Since the toggle is handled by DAX measures, all visuals on the page including Pie Charts and Bar Charts will update automatically based on the selected option (Month or Yesterday).

Just make sure you’re using the dynamic measures in the visual’s Values field.

The disconnected table is only used for selection it won’t affect the visuals directly, but your measures will control what data is shown.

The only thing to watch for is that DirectQuery might have some delay depending on your data source size, but functionally it works fine.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so

 other members can easily find it.

Best Regards,
Tejaswi.