Forum Discussion

Bian's avatar
Bian
Helper II
4 years ago
Solved

Semi-additive max date

Hi,   I have used Alberto and Marcos DAX pattern for Semi-additive calculations Semi-additive calculations – DAX Patterns Primaraly the DAX code to calculate Balance per customer. I need to add ...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    So something like this?

    Customers Lost =
VAR EndDate = DATE ( 10, 30, 2021 ) // Or from filter context
VAR MaxChangeDates =
    ADDCOLUMNS (
        SUMMARIZE ( Subscription, Customers[Name] ),
        "@MaxChangeDate", CALCULATE ( MAX ( Subscription[ChangeDate] ) )
    )
VAR AddEndDateCol =
    ADDCOLUMNS (
        MaxChangeDates,
        "@MaxEndDate",
            CALCULATE (
                MAX ( Subscription[EndDate] ),
                TREATAS ( { @MaxChangeDate }, 'Date'[Date] )
            )
    )
RETURN
    COUNTROWS ( FILTER ( AddEndDateCol, [@MaxEndDate] = EndDate ) )

    ( ^^ Not tested but hopefully gives an idea for the logic, at least.)

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

So something like this?

Customers Lost =
VAR EndDate = DATE ( 10, 30, 2021 ) // Or from filter context
VAR MaxChangeDates =
    ADDCOLUMNS (
        SUMMARIZE ( Subscription, Customers[Name] ),
        "@MaxChangeDate", CALCULATE ( MAX ( Subscription[ChangeDate] ) )
    )
VAR AddEndDateCol =
    ADDCOLUMNS (
        MaxChangeDates,
        "@MaxEndDate",
            CALCULATE (
                MAX ( Subscription[EndDate] ),
                TREATAS ( { @MaxChangeDate }, 'Date'[Date] )
            )
    )
RETURN
    COUNTROWS ( FILTER ( AddEndDateCol, [@MaxEndDate] = EndDate ) )

( ^^ Not tested but hopefully gives an idea for the logic, at least.)

Bian's avatar
Bian
Helper II
4 years ago

This was so helpful! Thank you very much.

I get correct Count but not correct month.


Count is now shown when the change was made. I wan't it to show in the month of the EndDate.

Not realy sure if i should try to change te linage somewhere in the end or if the change should be made in the base.

 

My Date Table is related to ChangeDate.