Forum Discussion

jesuslogmein's avatar
jesuslogmein
Icon for Helper I rankHelper I
3 years ago
Solved

Subtotals per date

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's avatar
    v-zhangti
    3 years ago

    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.

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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. 
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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.