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 guys sorry in advance if this is a silly questions I am still trying to get the grasp of DAX
I have the following dataset
Store | Ordered items | Price per item | Date ordered |
AA | 89 | £ 9.00 | 01/01/2022 |
AA | 52 | £ 9.00 | 01/02/2022 |
AA | 66 | £ 9.00 | 03/03/2022 |
AA | 100 | £ 9.00 | 02/02/2022 |
BB | 6020 | £ 9.00 | 02/02/2022 |
BB | 2525 | £ 9.00 | 02/02/2022 |
BB | 1059 | £ 9.00 | 02/02/2022 |
BB | 10002 | £ 9.00 | 01/06/2019 |
CC | 512 | £ 9.00 | 01/05/2023 |
CC | 9999 | £ 9.00 | 01/01/2020 |
CC | 25 | £ 9.00 | 01/01/2001 |
I am trying to calculate year on year growth however I am not sure what I am doing wrong as I cant get it to work.
I have also created a calendar table and there is a relationship between them.
I am using the following formula and can't figure out what is wrong with it.
YearonYear= DIVIDE( SUMX('Aggregated data', 'Aggregated data'[Price per item] * 'Aggregated data'[Ordered qty]), CALCULATE( SUMX('Aggregated data', 'Aggregated data'[Price per item] * 'Aggregated data'[Ordered qty]), SAMEPERIODLASTYEAR('CALENDAR'[Date]) ) )
Any help would be much appreciated
Try
YearonYear =
VAR LastYear =
CALCULATE (
SUMX (
'Aggregated data',
'Aggregated data'[Price per item] * 'Aggregated data'[Ordered qty]
),
SAMEPERIODLASTYEAR ( 'CALENDAR'[Date] )
)
VAR ThisYear =
SUMX (
'Aggregated data',
'Aggregated data'[Price per item] * 'Aggregated data'[Ordered qty]
)
VAR Result =
DIVIDE ( ThisYear - LastYear, LastYear )
RETURN
Result