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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors