Grouped sum on condition

[AdityaPowerBI]

Hi All,

I am facing a strange aggregation problem. I have an amount field which I need to filter for a date range selection and this amount field should sum for each date.

E,g Input Data

Date Amount
20Apr  100

20 Apr  200

25 June 300
20Dec 500
Output: for date range selected 1st April to 30th June
Amount   600
. I have a table visual where there are many field.I do not want the date field in the output as it will change the grain of the data but want to aggregate for each date. Basically my measure should sum for each date but ignore all the fields present in the table visual

Output should be roughly of a window function in sql
SELECT ,SUM(AMOUNT) OVER (PARTIION BY DATE)
WHERE DATE > MIN(DATE1)--Selected start date
AND DATE <= MAX(DATE1) -- Selected end date

[Fowmy · Accepted solution]

@AdityaPowerBI 

Can you try the following measure? You can keep the date and other columns in the visual.

Total = 
CALCULATE(
    SUM(Table[Amount]),
    ALLEXCEPT(Table,Table[Date])
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[v-henryk-mstf · Accepted solution]

Hi @AdityaPowerBI ,

According to your request, you want to use DAX to implement the calculation logic of SQL formula in PBI. I did the following test to sum the value of the Amount column based on the Date.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy · Accepted solution]

@AdityaPowerBI 

Can you try the following measure? You can keep the date and other columns in the visual.

Total = 
CALCULATE(
    SUM(Table[Amount]),
    ALLEXCEPT(Table,Table[Date])
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[amitchandak]

@AdityaPowerBI , Looking at data

a measure like this should work 

calculate(Sum(Table[Date]))

Or PARTITION by Date

sumx(values(Table[Date]), calculate(Sum(Table[Date])))

An independent Date file
measure =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
return
calculate(sumx(values(Table[Date]), calculate(Sum(Table[Date]))), filter( Table, Table[Date] >=_min && Table[Date] <=_max))

Joined date filter

measure =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
return
calculate(sumx(values(Table[Date]), calculate(Sum(Table[Date]))), filter( Date, Date[Date] >=_min && Date[Date] <=_max)) // there is not need of date filter if joined