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 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:
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |