Determining if a record falls on the first day of a quarter and if not collect it in the next Quarte
I have been presented with an interesting task, I have a dataset that contains all of our sales records and our company measures SSS (Same Store Sales) Year over year.
I solved this fairly easily by inputting the first RO date into the dataset for each location.
Now the accounting department has come back and said they really want to do this measure on a quarterly basis, so if that FirstRODate > Day 1 of the Quarter then don't pick it up until the next quarter.
So something like:
Case when Quarter(`FirstRODate`)=QUARTER(DAYOFMONTH('1'))then `OpsNetSales` Else 0
I know that Beast Mode doesn't work, I'm struggling with how to identify if the FirstRODate is actually the 1st day of the quarter in which it falls.
If that is to difficult I could also look at something that says if the MonthDay ('FirstRODate')<>1 then Quarter('FirstRODate') +1 Maybe some sort of concat statement that gives it a FirstROQuarter status.
I'm sure this is possible, just not sure of the best approach.
Thanks for your help