Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
78 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
84 | |
70 |