Forum Discussion

Khomotjo's avatar
Khomotjo
Helper II
1 year ago
Solved

Summarize Incremental Data

Hello Everyone,

 

I have data that looks like this :

 

I would like to create a table that looks like this :

I tried to create a measure like this but I am not getting the correct numbers :

Finalised = CALCULATE(
    COUNT(Orders[Reference]),
    FILTER(ALL(Orders),(TRUNC(Orders[Finalised Date])=TRUNC(Orders[Date]))
)
)
 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Khomotjo , hello bhanu_gautam, thank you for your prompt reply!

     

    To resolve your issue, verify the following steps:

     

    • Create a Date Table: You could create a date table that contains all possible dates and link it to your data model. Then, you can use this date table to show all dates.

     

    DateTable = CALENDAR(MIN('Order'[Finalised]), MAX('Order'[Date]))

     

     

    •  Calculation Measure: Calculate the count of orders based on Finalised Date but make sure that for missing dates, you return a 0. Here's a possible DAX solution using a date table:

    FinalisedCount = COALESCE(
CALCULATE(
    COUNTROWS('Order'),
    FILTER(
        'Order',
        TRUNC('Order'[Finalised]) = TRUNC(MAX(DateTable[Date]))
    )
),0)
​

    Result for your reference:

    Best regards,

    Joyce

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

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Khomotjo 

    Here is a revised version of the measure:

    Finalised =
    CALCULATE(
    COUNT(Orders[Reference]),
    FILTER(
    ALL(Orders),
    Orders[Finalised] = Orders[Date]
    )
    )


    This measure counts the number of orders where the Finalised date matches the Date for each row in the Orders table.

     

    To create the summary table, you can use the following DAX code:

    DAX
    SummaryTable =
    SUMMARIZE(
    Orders,
    Orders[Date],
    "Finalised", [Finalised]
    )
    This will create a new table with the Date and the count of finalized orders for each date.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Khomotjo , hello bhanu_gautam, thank you for your prompt reply!

     

    To resolve your issue, verify the following steps:

     

    • Create a Date Table: You could create a date table that contains all possible dates and link it to your data model. Then, you can use this date table to show all dates.

     

    DateTable = CALENDAR(MIN('Order'[Finalised]), MAX('Order'[Date]))

     

     

    •  Calculation Measure: Calculate the count of orders based on Finalised Date but make sure that for missing dates, you return a 0. Here's a possible DAX solution using a date table:

    FinalisedCount = COALESCE(
CALCULATE(
    COUNTROWS('Order'),
    FILTER(
        'Order',
        TRUNC('Order'[Finalised]) = TRUNC(MAX(DateTable[Date]))
    )
),0)
​

    Result for your reference:

    Best regards,

    Joyce

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