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

Bian's avatar
Bian
Helper II
4 years ago

 

Allright so here is an example from the table

So its a Subscription model and this is the stat-table holding all changes to every subscription. It's currently around 400K rows. 

 

ChangeDateCustomerIDSubscriptionIDProductIDStartDate

EndDate

01-01-20211115501-01-200910-30-2021
02-10-20211115501-01-200910-30-2022
10-12-20211115501-01-200910-30-2021

 

I wan't to calculate Total Customers with an EndDate in every month. But I only wan't the last change.
Above example should amount to 1 customer in october 2021.

 

Grateful for any assistance

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

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago
        Bian wrote:

        I get correct Count but not correct month.

        What does this mean? I didn't write a measure to return a month.