Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Good Morning,
I am attempting to write a DAX Command to create new columns for Sales from a specific year. I am attempting to compare YOY data from today back to 1998 on a line or bar graph with the Y axis being Sales $ and the X Axis being the month. I would also like to be able to filter those columns by Customer. Currently I have the following expression and it is not working correctly:
Solved! Go to Solution.
😐 Oops...that's what I get for doing this in Notepad.
This instead
2021 Sales =
CALCULATE(
[Total Sales],
FILTER(
ALLEXCEPT(
SALES,
CustomerID
),
SALES[InvoiceDate].[Year]= 2021
),
FILTER(
'Customer',
'Customer'[CustomerID] = SELECTEDVALUE('Customer'[CustomerID])
)
)
It's returning the same number for all Customers because you used ALL() in the FILTER function.
Try this...
2021 Sales =
CALCULATE(
[Total Sales],
FILTER(
ALLEXCEPT(
SALES,
CustomerID
),
SALES[InvoiceDate].[Year]= 2021
),
'Customer'[CustomerID] = SELECTEDVALUE('Customer'[CustomerID])
)
assumes you have a field named CustomerID
Unfortunately, that is returning the following error :
A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
😐 Oops...that's what I get for doing this in Notepad.
This instead
2021 Sales =
CALCULATE(
[Total Sales],
FILTER(
ALLEXCEPT(
SALES,
CustomerID
),
SALES[InvoiceDate].[Year]= 2021
),
FILTER(
'Customer',
'Customer'[CustomerID] = SELECTEDVALUE('Customer'[CustomerID])
)
)
Thank You! That worked perfect.
You're welcome. Glad I could help! 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
58 | |
19 | |
12 | |
11 | |
10 |