Forum Discussion

stoic1618's avatar
stoic1618
New Member
9 years ago
Solved

Slice by dates for Each Year

Hi, 

I wish to calculate the total revenue for a specific time period for each year. So if I have data for 4 years 2014, 2015, 2016, and 2017, I want to be able to check the total revenue generated in each year from say, Jan 25th to July 25th. The time period can be entered by user, so it's adjustable. I came across a lot of posts regarding year to date calculation, but most of them were for fixed time periods or quarters. So I thought I'd post here to know for sure if there's a way to do this on power bi

 

Thanks 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi stoic1618,

     

    You can refer to below sample to achieve your requirement.

     

    1. Creata a table as the source of slicer.(month no)

    Selector = SELECTCOLUMNS(VALUES('Table'[Date].[MonthNo]),"Month",[Date].[MonthNo])

    2. Write a measure to calculate based on slicer.

    dynamic = 
var select_Min=MINX(ALLSELECTED(Selector[Month]),[Month])
var select_Max=MAXX(ALLSELECTED(Selector[Month]),[Month])
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[Date]>=DATE(MAX('Table'[Date].[Year]),select_Min,1)&&[Date]<=DATE(MAX('Table'[Date].[Year]),select_Max,1)))

     

    3. Create the bar chart and slicer.

     

    Result:

     

     

     

    Regards,

    XIaoxin Sheng

3 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi stoic1618

     

    What you could do is to use the Date Slicer which will then enable your user to use the slider to decide the date range that he wants to use.

     

    With the above you would only require one measure which would be you Total Revenue. Then as they change the Date Slider it will reflect the Total Revenue for that period?

    • stoic1618's avatar
      stoic1618
      New Member
      9 years ago

      Sorry, I was probably not clear in my question. The problem with using a date slicer was it wouldn't let me select just the date (of the month) and month. I had to select the entire date (along with the year). So that would filter the data for that particular year. This image should probably help: 

       

       

      This is what I'm doing for now as a temporary solution. As you can see, I want the revenue for each year, and I have selected it for some particular months. I want to be able to do that for any date (date and month).. I couldn't make the date slicer filter by just the date and month.  

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi stoic1618,

         

        You can refer to below sample to achieve your requirement.

         

        1. Creata a table as the source of slicer.(month no)

        Selector = SELECTCOLUMNS(VALUES('Table'[Date].[MonthNo]),"Month",[Date].[MonthNo])

        2. Write a measure to calculate based on slicer.

        dynamic = 
var select_Min=MINX(ALLSELECTED(Selector[Month]),[Month])
var select_Max=MAXX(ALLSELECTED(Selector[Month]),[Month])
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[Date]>=DATE(MAX('Table'[Date].[Year]),select_Min,1)&&[Date]<=DATE(MAX('Table'[Date].[Year]),select_Max,1)))

         

        3. Create the bar chart and slicer.

         

        Result:

         

         

         

        Regards,

        XIaoxin Sheng