Subtotals per date

[jesuslogmein]

HI!!

I need to add the data that appear at interval level in a section, for example at 14.00h, and in the rest of the sections that are empty.

An example would be:

DATE INTERVAL ORDERS

20/11/2022 11:00 2
20/11/2022 12:00 3
20/11/2022 13:00 2
20/11/2022 14:00 2
21/11/2022 11:00 3
21/11/2022 12:00 3
21/11/2022 13:00 3
21/11/2022 14:00 4

The result would be as follows:

DATE INTERVAL ORDERS

20/11/2022 11:00 
20/11/2022 12:00 
20/11/2022 13:00 
20/11/2022 14:00 9
21/11/2022 11:00 
21/11/2022 12:00 
21/11/2022 13:00 
21/11/2022 14:00 13

Can we do it with dax? How would we do it?

[v-zhangti · Accepted solution]

Hi, @jesuslogmein 

You can try the following methods.
Column:

Column = 
Var _sum=CALCULATE(SUM('Table'[ORDERS]),ALLEXCEPT('Table','Table'[DATE]))
Var _max=CALCULATE(MAX('Table'[INTERVAL]),ALLEXCEPT('Table','Table'[DATE]))
Return
IF([INTERVAL]=_max,_sum,BLANK())

Measure = 
Var _sum=CALCULATE(SUM('Table'[ORDERS]),ALLEXCEPT('Table','Table'[DATE]))
Var _max=CALCULATE(MAX('Table'[INTERVAL]),ALLEXCEPT('Table','Table'[DATE]))
Return
IF(SELECTEDVALUE('Table'[INTERVAL])=_max,_sum,BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[FreemanZ]

@jesuslogmein 

Something like this?

you would need to add a new column with the code below:

OrdersAccum =

VAR CurrentDate = TableName[Date]

VAR ExpectedInterval = TIME(14,0,0)

RETURN

IF(

    TableName[Interval] = ExpectedInterval,

    SUMX(

        FILTER(

            TableName,

            TableName[Date]=CurrentDate

        ),

        TableName[Orders]

    )

)

Be careful with data type of the interval column, i chose Time type. 

[jesuslogmein]

many thanks!!!