cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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  @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

5 REPLIES 5
Community Support

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

Super User

@Anonymous

here is a workarond for you

pls see the attachment below

Proud to be a Super User!

Anonymous
Not applicable

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!

Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors