Summarize Incremental Data

[Khomotjo]

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]))

)

)

[Anonymous · Accepted solution]

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]

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