Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 m...
Need Help
v-robertq-msft's avatar
v-robertq-msft
Community Support
5 years ago

Hi, Anonymous 

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:

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

     

     

     

    • v-robertq-msft's avatar
      v-robertq-msft
      Community Support
      5 years ago

      Hi, Anonymous 

      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.