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
Anonymous
Not applicable

Can't figure out DAX for findng values for previous orders from last year

Hi,

I am trying to create a table where a salesperson can go in and find some numbers. In an order this is what it logically should do in my head:

 

1) Salesperson selects a week in a slicer which is current for this year.

2) Based on that week only the item no and related year should be returned.

3) Fetch the current Sales values for those rows and based on the Year and ItemNo filtered down in Step 2 fetch in Sales for the previous year in a new column.


Picture of sample data:

 

Capture.JPG

The data model is as following:

Fact_Sales is connected to Issue_DIM (* : 1)
Issue_DIM is connected to DIM_Time (* : 1)

Columns in tables:
Fact_Sales: Sales
DIM_Issue: ItemNo, ReturnWeek, ReturnWeek_Year (Concatenated between Year from DIM_Time and ReturnWeek)
DIM_Time: Year


I think I am fumbling around with the order of how I am writing the DAX and which functions I am using. I am trying to do this with a measure.

 

Any thoughts, ideas, links to articles or solutions would be greatly appreciated.

 

Best,

 

Ali A

5 REPLIES 5
amitchandak
Super User
Super User

Usually last year same week 364 days behind

Way 1

Last Year Same Week Day Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR(DATEADD('Date'[Date Filer],-1,DAY)))
Last Year WTD Sales = 
Var _year= year(maxx('Date','Date'[Date Filer]))-1
Var  _week_num =maxx('Date',WEEKNUM('Date'[Date Filer]))

Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),FILTER(Sales,year(Sales[Sales Date])=_year && WEEKNUM(Sales[Sales Date])=_week_num))
return
_last_year_val

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

Anonymous
Not applicable

Hi,

 

First of all thanks for responding @amitchandak !

Just to clarify. It isn't a same period last year calculation in terms of following dates and that is what makes it difficult.

 

The Return Week filter is to say that I want to see what was the final sales numbers based on when we had the results returned to us this year, and then for those issue id's and year I want to see the same info from last year.

The returnweek is merely to find out what issue id's I am filtering down. The Sales LY could be from a different week because the final sales numbers were reported at a different week last year than what it was this current year.

So if I go in and chose to see what final numbers were reported back in Week 35 i would proceed as following:

1) Choose week 35 in a slicer

2) I then get a list of the current order id's that were made this year and their corresponding id's

3 The last year sales is calculated based on order id's = last year's order id's, but with numbers from previous year.

 

Notice in my example the first row isn't in the example of what I am trying to achieve because:

1. It isn't a row for "current year" which is what I first want to isolate

2.  The ItemNo doesn't correspond to any ItemNo's in the current year which I am trying to isolate, as mentioned before.

 

Thanks again,


Ali A

 

 

So you finally want to get Item 37 which sold last year but not is this year. Or Item 41 which sold this year but not last year.

 

In both case, you can write a formula use visual level filter 

filter(sales,isblank(this_year) && not(isblank(last_year)))

 

I think I am still missing some context.

Anonymous
Not applicable

No,

 

the final result should only have row 6 and 7 because when you want to see a report from this year and the return week for that year you so you are isolating rows with the combination 2019-36

 

From there:

 

1) You chose 2019 and then ReturnWeek 36 and are left with Row 6 and 7

2) Now you want to find the Sales for the last year based on the year you have sliced down to (2019 in this case) so that leaves us with:

 

D13 gets its value from C5 because the year is 2018 and ItemNo is 40 (same ItemNo, except for previous year)

D14 gets nothing from last year because it has no corresponding ItemNo from the last year, but current year should still show up because it has sales from current year (2019).

 

Does that make sense?

What is the formula you tried on power BI? Can you please share. Also, share the screenshot of what you are getting there after hiding the sensitive number.

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.