Forum Discussion
Group Data Between a Date Range
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!
2 Replies
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)))
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!
