Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ArBohe
Regular Visitor

Sameperiodlastyear with only year and week

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

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Anonymous
Not applicable

Would using values not accomplish the same thing?

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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] )
)

Values as sep filter.png

Anonymous
Not applicable

Ah, yeah, forgot for totals and subtotals VALUES would provide more than 1 value.  Good catch, thanks Smiley Happy

Anonymous
Not applicable

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:

Prev Year no Time Intell.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.