Slice by dates for Each Year

[stoic1618]

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 

[GilbertQ]

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]

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.  

[v-shex-msft · Accepted solution]

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