Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I would like to do a measure that calculate sales of same period last year.
now i use this:
Venduto PY = CALCULATE(
SUM('Vendite'[YY. Valore Tot. Documento]);SAMEPERIODLASTYEAR(Calendario[Date])
)
but in this way it considers the sales untill the end of the month. I would like to have sales for example from 01/01/2019 to 04/07/2019 (todaY)
Thanks for the help.
Regards
Solved! Go to Solution.
Hi @nannimora ,
Sorry to reply late. Please check:
Sales PY 2 =
VAR LastDay =
LASTDATE ( 'Calendar'[Date] )
VAR LastYearDay =
DATEADD ( LastDay, -1, YEAR )
RETURN
CALCULATE (
[Sales],
SAMEPERIODLASTYEAR ( 'Calendar'[Date] ),
'Calendar'[Date] <= LastYearDay
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
no the problem isn't solved.
I have modified the pbix posted by @Icey
The Calendar Table now is:
Calendar =
CALENDAR(
MIN('Value Table'[Date]);
MAX('Value Table'[Date])
)
and i added two new measures:
Sales = CALCULATE(SUM('Value Table'[Value]))
Sales PY = CALCULATE([Sales];SAMEPERIODLASTYEAR('Calendar'[Date]))
Now as u can see.
the matrix shows me the weeks and the week n. 15 of the previous year sums all the value untill the end of the months.
Es. week 15, columns "Sales PY" = 205.
Istead of the right value that has to calculate until 08/04/2019.
I hope you could undestand what I tried to explain. Sorry for my elementary English 🙂
Now I see that i am not able to insert a .pbix files.
Thank's for the support.
Hi @nannimora ,
Sorry to reply late. Please check:
Sales PY 2 =
VAR LastDay =
LASTDATE ( 'Calendar'[Date] )
VAR LastYearDay =
DATEADD ( LastDay, -1, YEAR )
RETURN
CALCULATE (
[Sales],
SAMEPERIODLASTYEAR ( 'Calendar'[Date] ),
'Calendar'[Date] <= LastYearDay
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Now is perfect. Thank you so much for the support!!
Hi @nannimora ,
How about creating measures like so:
Sum Measure ( 2020 ) =
CALCULATE (
SUM ( 'Value Table'[Value] ),
YEAR ( 'Calendar'[Date] ) = YEAR ( TODAY () )
&& 'Calendar'[Date] <= TODAY ()
)
Sum Measure ( 2019 ) =
VAR ThisYear =
YEAR ( TODAY () )
VAR LastYear = ThisYear - 1
VAR LastYearToday =
DATE ( LastYear, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
CALCULATE (
SUM ( 'Value Table'[Value] ),
YEAR ( 'Calendar'[Date] ) = LastYear
&& 'Calendar'[Date] <= LastYearToday
)
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
SAMEPERIODLASTYEAR uses the period displayed in your table.
So if your table is "by month", SAMEPERIODLASTYEAR takes the whole month.
If you make a table by day, it should work... But maybe yuo want it by month ?
Other solution is to use a different function, maybe calculating a start date and end date, using DATEADD ?
Like here :
https://community.powerbi.com/t5/Desktop/Dax-Calculate-Min-amp-Max-of-Dates/td-p/502929
Is that of any help ?
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
113 | |
105 | |
85 | |
65 | |
64 |