Need help with comparable summing of sales

[percy83]

Hi all,

I need some help figuring out how to sum sales for only comparable units. 

The definition of "comparable" is when a store unit has sales generated in an equivalent time period from last year.

The report I am building has slicer that defines the selected time period that will be used as a benchmark.

My model is a star schema with sales, date and store info

Super easy example is that if the bench slicer has the value "7" selected the store has to be open for at least 365 days + 7 days (or what every I select in the bench slicer) . 

Bench slicer = 

Sales trend days range = IF(HASONEVALUE(PeriodSlicer[Days]);VALUES(PeriodSlicer[Days]);COUNTROWS(dDate))

Opening date is  dStoreInfo[OpeningDate]

Sales is a measure [Sales]

Thanks for any help I can get! 

Regards

[v-shex-msft]

HI @percy83 ,

Maybe you can take a look at the following blog to know more about use date function to achieve rolling total:

Time Intelligence "The Hard Way" (TITHW)  

Regards,
Xiaoxin Sheng

[amitchandak]

Initially, it seems like you need datesytd

https://docs.microsoft.com/en-us/dax/datesytd-function-dax

Year  = CALCULATE(SUM(table[column]),DATESYTD('Date'[Date Filer]))
Last Year  = CALCULATE(SUM(table[column]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))

But from this, it does not seems like that. Can you put more light on the same?

---

@percy83 wrote:

Super easy example is that if the bench slicer has the value "7" selected the store has to be open for at least 365 days + 7 days (or what every I select in the bench slicer) . 

Bench slicer = 

Sales trend days range = IF(HASONEVALUE(PeriodSlicer[Days]);VALUES(PeriodSlicer[Days]);COUNTROWS(dDate))

Opening date is  dStoreInfo[OpeningDate]

Sales is a measure [Sales]

Thanks for any help I can get! 

Regards

---

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601