Comparing sales over two periods of time

[arca123]

Hi!
I was wondering what's the best way to compare two data periods of sales. I am looking into ''Same Store Sales''.
I tried using a measure like:

SSS = divide([Sales],CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date])))-1 

To get a comparion percentage for this year vs last year. However, it appears that this measure does not work as intended. Currently, for February 2024 I have sales data for 13 days, but for February 2023 I have sales data for 28 days. So this measure sums up 28 days of sales data for the division, instead of taking the same period of first 13 days of February.

How can I make this measure work as intended?

[amitchandak · Accepted solution]

@arca123 , if you have selected Feb-2024 in slicer or Row, then it will get full Feb-2023

Try to use for last year sales

CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date]), 'Calendar'[Date] <= Date(year(Today()) -1, month(Today()),day(Today()) ) )

or

if(max('Calendar'[Date]) <= Date(year(Today()) -1, month(Today()),day(Today()) ), CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date])))

[amitsoftweb]

Hello

Here is our queries to achieve your requirements. Also Attached pbix file for your reference you can check it.

You can create measures on the basis of our queries and you can modified according to your dataset fields name.

---------------------------------------------------------------

Sales_This_Month_13_Days =

CALCULATE(

   SUM(Sheet1[Sales]),

   FILTER(

       'Sheet1',

       'Sheet1'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) &&

       'Sheet1'[Date] <= TODAY()

   )

)

-----------------------------------------------------------------------

Sheet1_Last_Year_13_Days =

CALCULATE(

   SUM('Sheet1'[Sales]),

   FILTER(

       'Sheet1',

       'Sheet1'[Date] >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1) &&

       'Sheet1'[Date] <= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))

   )

)

------------------------------------------------------------------------------------------------------------

Comparing % = DIVIDE(([Sales_This_Month_13_Days]-[Sheet1_Last_Year_13_Days]),[Total sale])*100

------------------------------------------------------------------------------------------

Current % = DIVIDE([Sales_This_Month_13_Days],[Total sale])*100

----------------------------------------------------------------------------

Last % = DIVIDE(([Sheet1_Last_Year_13_Days]),[Total sale])*100

For more details you can reach out to us: Power BI Consulting services

[v-yilong-msft · Accepted solution]

Hi @arca123 ,

In response to your question, here is my thought process.

Create a simple table like this.

Create a measure to compare sales over two periods of time.

Measure = 
VAR _2023 = CALCULATE(SUM(T1[Sales]),T1[Year]="2/1/2023") / 28
VAR _2024 = CALCULATE(SUM(T1[Sales]),T1[Year]="2/1/2024") / 13
RETURN
_2024/_2023

Finally, you will see the result.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@arca123 , if you have selected Feb-2024 in slicer or Row, then it will get full Feb-2023

Try to use for last year sales

CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date]), 'Calendar'[Date] <= Date(year(Today()) -1, month(Today()),day(Today()) ) )

or

if(max('Calendar'[Date]) <= Date(year(Today()) -1, month(Today()),day(Today()) ), CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date])))