Forum Discussion
SUMIFS
Hi Julier The SUMIFS formula returns a #VALUE! error due to the table layout with day numbers in columns.Please try these
Use Power Query to unpivot the day columns, transforming the data into a tabular format with columns for Customer, Day, and Sales.
Apply a conditional column or filter to select the desired day range.
Use a SUM measure in DAX:
Total Sales = CALCULATE(SUM(Sales[Value]), Sales[Day] >= 8, Sales[Day] <= 14, Sales[Customer]
Julier Create a table that contains the day numbers. This can be done in Power BI using the "Enter Data" feature or by creating a calculated table.
DateRange = GENERATESERIES(1, 31, 1)
Create a table that contains the unique customer names. This can be done using the "Enter Data" feature or by creating a calculated table.
CustomerTable = DISTINCT('Sales'[Customer])
Add slicers to your report for selecting the customer and the date range. Use the CustomerTable for the customer slicer and the DateRange for the date range slicer.
Use DAX to create a measure that dynamically calculates the sales based on the selected customer and date range
DAX
DynamicSales =
VAR SelectedCustomer = SELECTEDVALUE('CustomerTable'[Customer])
VAR StartDay = MIN('DateRange'[Value])
VAR EndDay = MAX('DateRange'[Value])
RETURN
CALCULATE(
SUM('Sales'[Sales]),
'Sales'[Customer] = SelectedCustomer,
'Sales'[Day] >= StartDay,
'Sales'[Day] <= EndDay
)
Thank you for you quick response, however i need to do this in excel
Hi Julier If this is in excel use SUMPRODUCT please
=SUMPRODUCT(($A$2:$A$4="Customer A")*(B$1:Z$1>=8)*(B$1:Z$1<=14)*(B2:Z4))Julier , Try using
excel
=SUMIFS(B2:X2, $A$2:$A$4, "CUSTOMER A", $B$1:$X$1, ">=1", $B$1:$X$1, "<=7")=SUMIFS(B3:X3, $A$2:$A$4, "CUSTOMER B", $B$1:$X$1, ">=8", $B$1:$X$1, "<=14")