Query - Filtering and Sum

[Anonymous]

Hi, I am new to Power Bi,

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 or query?

I can post data if need be

[v-frfei-msft · Accepted solution]

Hi @Anonymous ，

Setting the slicer to between as below should help you.

[Anonymous]

Hi   

That will work; however, I am producing my data in a matrix along with other measures. I am replicating a report in Power BI from Excel. See picture of data below. 

Hope this helps. 

@v-frfei-msft @Anonymous 

[Anonymous]

NetGroup2 = 
VAR StartDateNet = CALCULATE(SUM(Test[Quantity]),Test[Brand]="Group2",ALLSELECTED(Test[StartDate]))
VAR ServiceEndDateNet = CALCULATE(SUM(Test[Quantity]),Test[Brand]="Group2",ALLSELECTED(Test[StartDate],Test[ServiceEndDate]))
RETURN StartDateNet

[Anonymous]

Hi Vimal, 

Thank you for you're help so far. 

I have more than 1 brand in my data that I need to include. I can sort this by adding a filter on the visual.

My main concern is that is there no way to add a date field such as "DatesBetween" in the below dax expression? I want to Power BI to give me net adds for September only. @Anonymous