Sum and filter

[Anonymous]

Hi,

I am trying to work out my net adds for the month.

• In excel I filter on the Start Date column i.e. October dates only.
• I then need to sum the quantity column.
• Leaving the filter on the Start Date column I then filter on the service End Date Column for October dates only. – I then sum the quantity column
• I now have two sum figures
• I take away the SumQuantity for Start Date & SumQuantity for Service End Date to work out my net adds

Can anyone advise which would be the best Dax formula for this?

I can post data if need be.

[technolog · Accepted solution]

To calculate the net adds for the month based on filtering by the Start Date and Service End Date columns and summing the Quantity column in Power BI using DAX, you can create two measures and then calculate the net adds. Here’s how you can do it:

Step 1: Create Measure for Start Date Sum
First, create a measure to sum the Quantity column where the Start Date is in the selected month (e.g., October).

StartDateSum =
CALCULATE(
SUM('YourTable'[Quantity]),
FILTER(
'YourTable',
MONTH('YourTable'[Start Date]) = MONTH(TODAY()) &&
YEAR('YourTable'[Start Date]) = YEAR(TODAY())
)
)
Step 2: Create Measure for Service End Date Sum
Next, create a measure to sum the Quantity column where the Service End Date is in the selected month (e.g., October).

EndDateSum =
CALCULATE(
SUM('YourTable'[Quantity]),
FILTER(
'YourTable',
MONTH('YourTable'[Service End Date]) = MONTH(TODAY()) &&
YEAR('YourTable'[Service End Date]) = YEAR(TODAY())
)
)
Step 3: Create Measure for Net Adds
Finally, create a measure to calculate the net adds by subtracting the EndDateSum from the StartDateSum.NetAdds = [StartDateSum] - [EndDateSum]