Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
With only 2 "date-like" columns, YEAR (i.e: 2018) and WEEK (i.e.: 34), what formula can I use to get sales from year ago same week:
YEAR WEEK SALES
2018 34 1000
2017 34 980
I need
YEAR WEEK SALES SALES YAG
2018 34 1000 980
Any clue?
Thanks !
Arnaud
Solved! Go to Solution.
Hi @ArBohe,
It seems @Anonymous's solution missed something. Please also try this similar one.
Prev Year Sales = CALCULATE( SUM(Table1[Sales]), FILTER( ALL(Table1), TABLE1[YEAR] = MAX(Table1[Year]) -1 && Table1[Week] = max(Table1[Week]) ) )
Best Regards,
Dale
Hi @ArBohe,
It seems @Anonymous's solution missed something. Please also try this similar one.
Prev Year Sales = CALCULATE( SUM(Table1[Sales]), FILTER( ALL(Table1), TABLE1[YEAR] = MAX(Table1[Year]) -1 && Table1[Week] = max(Table1[Week]) ) )
Best Regards,
Dale
Hello,
if I have slicers in my page they won't affect the meassure, so if I want to see the sales only from China it will nor work, right?
If I have a date dim what could I do?
Thank you
Hi Nick,
It returned an error "A table of multiple values was supplied where a single value was expected." in my test. I think it should be a statement rather than a table that the Values returns.
Best Regards,
Dale
Thought about it and VALUES will work, but not where I had it originally, but as a separate filter. Just a different way to accomplish the same thing.
Previous Week Sales = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALL ( Table1 ), Table1[Year] = MAX ( Table1[Year] ) - 1 ), VALUES ( Table1[Week] ) )
Ah, yeah, forgot for totals and subtotals VALUES would provide more than 1 value. Good catch, thanks
Since you do not have a dedicted date table, cannot use the built in time-intelligence functions like SAMEPERIODLASTYEAR. But what you are looking for still can be done.
Prev Year Sales = CALCULATE( SUM(Table1[Sales]), FILTER( ALL(Table1), TABLE1[YEAR] = MAX(Table1[Year]) -1 && VALUES(Table1[Week]) ) )
Output:
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |