cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering Two Criteria in DAX, Returning Numbers and Dates to a Table

Hello all,

I'm fairly new to PBI and am really struggling to get the DAX to enable me to do the following:

I want to build a summary table visualisation like this:

IDPrice (last working day of last week_ (date)Highest (Previous Week)High (date)Lowest (Previous Week)Low (date)
ex122819/11/202122918/11/202121315/11/2021
ex28701/11/20219418/11/202121315/11/2021
ex322801/11/20217918/11/202121315/11/2021
ex422819/11/202122918/11/202121315/11/2021
ex522819/11/202122918/11/202121315/11/2021


The consolidated table I've built in Power Query which should act as source for this data, looks a bit like this where I have added weekday numbers and week numbers so these can be filtered in the DAX

DatePriceWeek NumberWeekday NumberID
10/11/202134440ex1
10/11/202164441ex1
11/11/202143450ex2
11/11/202157451ex2


For the first column, I started with this DAX but it's returning nothing in my table (below). I already have just one column with the different IDs.

Price End of Previous Week =
VAR LastWeekNumber = [Last Week Number]
Return
calculate(max('Consolidated IDs'[ID]), LastWeekNumber = ('Consolidated IDs'[WEEK_NUMBER] - 1), 'Consolidated IDs'[WEEKDAY_NUMBER] = max('Consolidated IDs'[WEEKDAY_NUMBER]))
 
I'm trying to return, for each ID, the price for the highest workday number of the previous week - based on weekday number of today() - 1. 
WHere am I going wrong?
1 ACCEPTED SOLUTION
PaulOlding
Super User
Super User

Hi @Anonymous 

What is [Last Week Number]?

I tried out your expression with this definition:

Last Week Number = MAX('Consolidated IDs'[WEEK_NUMBER])

 

In that case, the problem is this part:

LastWeekNumber = ('Consolidated IDs'[WEEK_NUMBER] - 1)

It should be a plus, not minus:

LastWeekNumber = ('Consolidated IDs'[WEEK_NUMBER] + 1)

an alternative way of expressing that, which is easier to follow in my opinion:

'Consolidated IDs'[WEEK_NUMBER] = LastWeekNumber - 1

 

 

View solution in original post

3 REPLIES 3
PaulOlding
Super User
Super User

Hi @Anonymous 

What is [Last Week Number]?

I tried out your expression with this definition:

Last Week Number = MAX('Consolidated IDs'[WEEK_NUMBER])

 

In that case, the problem is this part:

LastWeekNumber = ('Consolidated IDs'[WEEK_NUMBER] - 1)

It should be a plus, not minus:

LastWeekNumber = ('Consolidated IDs'[WEEK_NUMBER] + 1)

an alternative way of expressing that, which is easier to follow in my opinion:

'Consolidated IDs'[WEEK_NUMBER] = LastWeekNumber - 1

 

 

Anonymous
Not applicable

Thanks Paul. [last week number] = weeknum(today()) - 1
This basically solved my problem, although it opened up another - in the last row of the last column in my table where the date of the lowest price should pull through, it is blank. The dates above it do pull through, it's just this last entry oddly enough.

DAX to pull the date through of the lowest price is:

Lowest Price Date Previous Week =
VAR LastWeekNumber = [Last Week Number]
Return
calculate(MAX('Consolidated IDs'[Date]), 'Consolidated IDs'[WEEK_NUMBER] = LastWeekNumber, 'Consolidated IDs'[PRICE] = min('Consolidated IDs'[PRICE]))
 
I do not know why the date is not pulling through. 

@Anonymous 

You could try using TOPN to get lowest price date:

Low (date) = 
VAR _LastWeekNumber = [Last Week Number]
VAR _Lowest = 
TOPN(1,
    FILTER('Consolidated IDs', 'Consolidated IDs'[WEEK_NUMBER] = _LastWeekNumber - 1),
    'Consolidated IDs'[Price],
    ASC
    )
RETURN
MAXX(_Lowest, 'Consolidated IDs'[Date])

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!