Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
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.
Solved! Go to 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
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
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()
Proud to be a Super User!
I've updated to add a factSales table. I can't seem to get your suggestion to work.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
89 | |
87 | |
35 | |
35 |
User | Count |
---|---|
154 | |
100 | |
83 | |
63 | |
54 |