Calculate sum of data in a date range in Power BI table

[Hussain]

Hi,

I have a table like this:

I want an output table like this:

Meaning,  every row should show the "Consumption" summed over a date range (date range is provided by the user in a date range slicer.The slicer is not shown in the above screenshot) for all the meter types. The requirement is also that we show the date range in each row in  "From Date" and "To Date" columns

Thanks in advance.

[Anonymous · Accepted solution]

Hi @Hussain ,

Create two measures:

From Date = CALCULATE(MIN('Table'[Date]),ALLSELECTED('Table'))

To Date = CALCULATE( MAX('Table'[Date]),ALLSELECTED('Table'))

Put the four fields in the table, and then select the aggregation operation of Count in the ConsumptionCount field.

You can filter date by a slicer.

Best Regards,

Stephen Tao

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

[amitchandak]

@Hussain , Assume you have independent date slicer, measures like these can help

Max Date = MAXX(allselected('Date1'),'Date1' [Date])
Min Date = MinX(allselected('Date1'),'Date1' [Date])

measure =
VAR _max = MAXX(allselected('Date1'),'Date1' [Date])
var _min = MinX(allselected('Date1'),'Date1' [Date])

return
calculate(Sum(Table[ConsumptionCount]), filter(Table, Table[upload Date]>=_min && Table[upload Date]<=_max))