No correct SUM are displayed in the matrix

[Anonymous]

Hi,

I would like to show the "Number of Materials" up to the current day/month/year (depending on selection).

For this, I used the following DAX formula:

Materials = 
CALCULATE (
    DISTINCTCOUNT ( MaterialsFact[CiceroId] ),
    NOT ( MaterialsFact[State] IN { "DISCARDED", "CLOSED_ILL" } )
)

Materials Previous Date = 
VAR FirstVisibleDate =
    MIN ( 'DateDim'[Id])
VAR PreviousOrderDate =
    CALCULATETABLE (
        LASTNONBLANK (
            'DateDim'[FullDate],
            [Materials]
        ),
        'DateDim'[FullDate] < FirstVisibleDate
    )
VAR Result =
    CALCULATE (
        [Materials],
        PreviousOrderDate
    )
RETURN
    Result

Unfortunately the total amount (see the value for January or 2022) is not displayed correctly before I expand the hierarchy: (see the image below)

I mention that there are 2 tables. DateDim and MaterialsFact and the relationship is between: DateDim[Id] = MaterialsFact[DateId](integers data type).
It works ok when I use the Id column from DateDim BUT I would like to use the "FullDate" column in the Visual(Matrix).

Do you think you can help me with this problem?

Thanks! 🙂

[amitchandak · Accepted solution]

@Anonymous , Change the return of second one like this and try

return
sumx(values('Date'[Date]), Result)

[Anonymous]

Thanks @amitchandak.