cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Dynamic bin for dates

The Bin type group uses min/max of the data as the begin/end of the bin. 

 

Can I use dynamic value for example slicer selected start and end month for the bin?

 

e.g.

Start month:  Feb20

End month :  Oct20

Bin: 3 months

 

Bin 1: Feb20-Apr20
Bin 2: May20-Jul20
Bin 3: Aug20-Oct20

 

 

MikeKK_1-1606316029726.png

 

5 REPLIES 5
Community Support
Community Support

Hi, @MikeKK 

According to your description, I think you don’t need to use a bin to achieve this, you can use measure, you can try my steps:

  1. Create a Calendar table:
Date = CALENDARAUTO()

 

  1. Create a measure:
total sales in 3 months block =

var _selectedmonth=MONTH(MIN('Date'[Date]))

return

CALCULATE(

    SUM(Sales[Amount]),

    FILTER('Sales',

MONTH([Date])>=_selectedmonth&&MONTH([Date])<=_selectedmonth+2))

 

  1. Create a Slicer, place ‘Date’[Yaer] and ‘Date’[Month], then create a column chart, then place like this:

v-robertq-msft_0-1606723386912.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

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

Thank you.  What I want to achieve is to have the time axis in 3 months group, depends on the start month on slicer. The group/bin function does not work dynamically.

 

 

MikeKK_0-1606748305100.png

 

Hi, @MikeKK 

In my opinion, you can not achieve it through DAX, because the calculated column/table can not be created by the selected value of Slicer, so you have to use measure. Unfortunately, you can’t use the measure to perform as the axis or legend of a column chart, so I think you can not achieve this if you want to use Slicer.

More info about dynamic binning and grouping:

 

Best Regards,

Community Support Team _Robert Qin

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

Community Support
Community Support

Hi, @MikeKK 

In my opinion, the purpose of designing the “bin and group” is to display your data with the desired unit(like 3 months). If you use it with the Slicer, it is already dynamic. What’s more, you can not change the Max value and Min value of the bin group, it’s set according to the field you selected.

More info about using binning

 

Best Regards,

Community Support Team _Robert Qin

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

Agree with you.  The reason I need this is to display the total sales in 3 months block ( and display the month on axis), starting with month selected in the slicer.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors