topic Re: Subtotals per date in DAX Commands and Tips

Subtotals per date

jesuslogmein — Mon, 21 Nov 2022 15:47:13 GMT

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?

Re: Subtotals per date

FreemanZ — Tue, 22 Nov 2022 02:57:23 GMT

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.

Re: Subtotals per date

v-zhangti — Tue, 22 Nov 2022 03:16:32 GMT

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.

Re: Subtotals per date

jesuslogmein — Tue, 22 Nov 2022 07:45:38 GMT

many thanks!!!