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 nu...
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.

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.