cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mdoll49
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

InvoiceDateYR_DAYS
11/1/2022200
11/2/2022201
11/3/2022202
11/4/2022202
11/5/2022202
11/6/2022203
11/7/2022204
11/8/2022205
11/9/2022206
 
factSales
InvoiceDateREV
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
v-yangliu-msft
Community Support
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:

vyangliumsft_0-1669797032858.png

 

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

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
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:

vyangliumsft_0-1669797032858.png

 

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
Super User
Super User

@mdoll49 

here is a workarond for you

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

then you can change selectedvalue() to today()





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.