revenue by store opening date

[pani_victoria]

Hi, all!

I have a table of sales by day and store (@sales), I have a table of stores with opening and closing dates (@stores). These tables are related.
The report has a filter with period selection.

I would like to calculate revenue only for those stores whose opening date is less than the minimum period in the filter - 1 year.

For example:
The period selected in the filter is Jan 23-Dec 23, which means revenue for stores with an opening date less than Jan 22 should be calculated.

I started by creating a measure that calculates the date min period -1 year

selection_date = CALCULATE(min('Date'[Date]), DATEADD('Date'[Date], -1, YEAR))

Then I try to calculate the revenue

CALCULATE('#measures'[revenue],
SUMMARIZE('@sales', '@stores'[store]),
CALCULATETABLE(
SUMMARIZE('@sales', '@stores'[store]),
'@stores'[opening_date]<'#measures'[selection_date]))

But my measure doesn't work, I need help

[amitchandak · Accepted solution]

@pani_victoria ,

Measure =

var _min1 = Minx(allselected(Date), Date[Date])

Var _min = date(year(_min1), Month(_min1), day(_min1))

return

CALCULATE('#measures'[revenue],filter('stores', 'stores'[opening_date]<_min ))

If date of date table  is joined to some other column

Measure =

var _min1 = Minx(allselected(Date), Date[Date])

Var _min = date(year(_min1), Month(_min1), day(_min1))

return

CALCULATE('#measures'[revenue],filter('stores', 'stores'[opening_date]<_min ), all('Date') )

[pani_victoria]

Thank you! You are monsters!💪👍

[v-shex-msft]

Hi @pani_victoria ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

Measure = CALCULATE([revenue],filter(values(@stores[store]),'@stores'[opening_date]<[selection_date]))

[amitchandak · Accepted solution]

@pani_victoria ,

Measure =

var _min1 = Minx(allselected(Date), Date[Date])

Var _min = date(year(_min1), Month(_min1), day(_min1))

return

CALCULATE('#measures'[revenue],filter('stores', 'stores'[opening_date]<_min ))

If date of date table  is joined to some other column

Measure =

var _min1 = Minx(allselected(Date), Date[Date])

Var _min = date(year(_min1), Month(_min1), day(_min1))

return

CALCULATE('#measures'[revenue],filter('stores', 'stores'[opening_date]<_min ), all('Date') )