Group Data Between a Date Range

[NVG]

Hi all,

I am trying to figure out how to group sales data by the year based on what type of review it has gone through. My issue is I don't know how to account for a range of years, if the review spans more than just one year how can I assoicate the sales data with it correctly.

Group Type	Sales	Sales Year	Review Start Date	Review End Date	Review Type
1	2014	5000	2014	2016	A
2	2015	6000	2010	2013	B
3	2016	7000	2014	2017	C
4	2015	8000	2014	2016	A
5	2016	9000	2015	2016	A

In this example I would expect the following results for the time period 2014-2017 which I have set with a splicer.

Total Sales	35000
Review Type A	22000
Review Type B	0
Review Type C	7000
Not Reviewed	6000

Here the total sales is just the sum of the sales which they all fall betwen 2014 and 2017. Then you have sales for Review Type A that fall on the first review date, inbetween, and the final year. Review Type B has no sales becuase the review took place in a time frame prior to the sales year.

I would like to be able to group by Group Type and have totals for each Review Type as my final results as the Group Types will repeat in the full data set.  In the full data you would end up with Group Type 1-xxx and the following for each Group Type: Total Sales, Total for Review A, B, C and Total Not Reviewed.

Thank you for the help! 

[Anonymous]

You can use the following DAX to get a new table group by Review Type. Then you can use it to get the similar information as you expected. I did not find a way that could merge Not Reviewed information in to Review Type table. Maybe someone else could help.

ReviewSummary = GROUPBY(Review,Review[Review Type],"SalesSum",SUMX(CURRENTGROUP(),IF(Review[Review End Date]>=Review[Sales],Review[Sales Year],0)))

[NVG]

Thank you for the formula. I was able to replicate it for my data. I think I wasn't very clear on needing the Group Type as well, I didn't put it in my example solution like I should have.

I would like to also include the Group Type. In a perfect world the solution would look like the following:

For 2014-2017
Group Type	Total Sales	Review Type A	Review Type B	Review Type C	Not Reviewed
1	5000	5000	0	0	0
2	6000	0	0	0	6000
3	7000	0	0	7000	0
4	8000	8000	0	0	0
5	9000	9000	0	0	0

I can also just caculate the Not Reviewed column by doing a subtraction of Total Sales - All Review Types but I wasn't sure how to get table created in this format for a larger data set.

Thanks!