Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
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

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

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

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

@Anonymous 

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!




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.

then you can change selectedvalue() to today()





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

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. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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