cancel
Showing results for
Did you mean:
Frequent Visitor

## DAX expression for Yesterday's sales using selling days field

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.

1 ACCEPTED SOLUTION
Community Support

Hi  @mdoll49 ,

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

5 REPLIES 5
Community Support

Hi  @mdoll49 ,

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

Super User

here is a workarond for you

pls see the attachment below

Proud to be a Super User!

Frequent Visitor

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.

Super User

then you can change selectedvalue() to today()

Proud to be a Super User!

Frequent Visitor

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors