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

[Anonymous]

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:

ID	Price (last working day of last week_	(date)	Highest (Previous Week)	High (date)	Lowest (Previous Week)	Low (date)
ex1	228	19/11/2021	229	18/11/2021	213	15/11/2021
ex2	87	01/11/2021	94	18/11/2021	213	15/11/2021
ex3	228	01/11/2021	79	18/11/2021	213	15/11/2021
ex4	228	19/11/2021	229	18/11/2021	213	15/11/2021
ex5	228	19/11/2021	229	18/11/2021	213	15/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

Date	Price	Week Number	Weekday Number	ID
10/11/2021	34	44	0	ex1
10/11/2021	64	44	1	ex1
11/11/2021	43	45	0	ex2
11/11/2021	57	45	1	ex2

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?

[PaulOlding · Accepted solution]

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]

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. 

[PaulOlding]

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