Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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])))
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.
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
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.
@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])))
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |