Forum Discussion

NVG's avatar
NVG
Frequent Visitor
8 years ago

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...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    NVG
    Frequent Visitor
    8 years ago

    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 TypeTotal SalesReview Type AReview Type BReview Type CNot Reviewed
    150005000000
    260000006000
    370000070000
    480008000000
    590009000000

     

    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!