DAX expression for Yesterday's sales using selling days field

[Anonymous]

I have a calendar table with a YR_Days field which assigns a selling day number for every invoice date. I need help with a DAX expression to calculate total yesterday sales using the YR_Days field. Example data below:

dimCalendar

InvoiceDate	YR_DAYS
11/1/2022	200
11/2/2022	201
11/3/2022	202
11/4/2022	202
11/5/2022	202
11/6/2022	203
11/7/2022	204
11/8/2022	205
11/9/2022	206

factSales

InvoiceDate	REV
11/1/2022	$5
11/2/2022	$25
11/3/2022	$5
11/4/2022	$10
11/5/2022	$60
11/6/2022	$32
11/7/2022	$20
11/8/2022	$4
11/9/2022	$17

In the example above, looking at a report for yesterday's sales on 11/6 would show a combined total ($75) for 11/3-11/5 since they all have a selling day of 202. However, on 11/7 that report would only show yesterday's sales ($32) for 11/6 (203).

Any help would be greatly appreciated!

EDITED: added factSales table and updated description. Would love to put the total into a card to display whatever yesterday's sales were whenever the user looks at the card.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('dimCalendar'[InvoiceDate])
var _column=
SELECTCOLUMNS(
    FILTER(ALL(dimCalendar),
'dimCalendar'[YR_DAYS]=
SUMX(FILTER(ALL(dimCalendar),
'dimCalendar'[InvoiceDate]=_select),[YR_DAYS])-1),"1",[InvoiceDate])
return
SUMX(FILTER(ALL('factSales'),'factSales'[InvoiceDate] in _column),[REV])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ryan_mayu]

@Anonymous 

here is a workarond for you

pls see the attachment below

[Anonymous]

This is very close to what I am looking for. However, I want to simply calculate yesterday's sales without having to select a date. I plan on using this in a card, similar to showing MTD, QTD, YTD. 

I edited my original post for more clarity.

[ryan_mayu]

then you can change selectedvalue() to today()

[Anonymous]

I've updated to add a factSales table. I can't seem to get your suggestion to work.