Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Distribution with user-specified date intervals

Hi everyone!

I'm fairly new to DAX and Power BI. I'm struggling with a particular client requirement where they would like to choose whether they see distribution data in monthly intervals, 2-month intervals, 3-month, 2-years and so on. I have tried creating bins for multiple possibilities and this seems to give me the desired visual however it's quite pedantic for the user to then drill-through these various views to get to the desired interval they require at that time.

 

I think I need to create some calculated columns for each of the interval possibilities (2 months, 3 months etc) based on the Employee Hire date axis in my data and then create a dynamic x axis slicer so that the user can choose how they want to see the data. I'm not too sure how to go about doing this dynamically. I assume I will have to write some DAX.

 

This is what a couple of rows from my dummy data look like at the moment. I'm just focusing on the Employee Hire date for now ...

 

Employee Hire Date       Employee Termination Date         Employee ID

04/04/2017                      04/06/2018                                   0001234

04/05/2017                      06/05/2018                                   0003456

 

My final graph should have the Hire date on the x axis (with a slicer to choose the various month/year intervals) and the employee count for the various hire date intervals on the y-axis.

 

I should probably add that the user wants to be able to choose the date range and then view the data within that range in the desired interval (2-month, 3-month etc).

 

Any help appreciated! I've been researching for days!

 

Thanks,

 

SM

Need Help

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    Based on your scenario, you could reference the thread  to have a try. If you want to show lots of date intervals, I’m afraid it is very complicated and difficult to solve it. Maybe you could share more details and post the excepted  output, then we can find other workaround.

     

    Best Regards,

    Xue Ding

    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
      7 years ago

      Thanks for that but the links didn't really get me to the method I used in the end. I used the same logic used behind calculating Yearly Quarters and defined bins for the various time categories I wanted to group my data by. The switch statement I used to create a "bimonthly" calculated column in the date table in my model:

       

      SWITCH( TRUE(),

                             DIM_CALENDAR[MONTH] = 1, "B1",

                              DIM_CALENDAR[MONTH] = 2, "B1",

                              DIM_CALENDAR[MONTH] = 3, "B2",

                               DIM_CALENDAR[MONTH] = 4, "B2"

                                (and so on...)

      BLANK()                                  )

       

      I modified this statement for  4-month, 6-month, 2 year, 3 year etc categories. It takes a bit longer to write year bins for your data if your data dates back decades! 

      I then created another calculated column where I concatenated these categories with their corresponding year and plotted that on the X-axis of my distribution graph. 

       

      There might be a quicker way but this worked well for me.