Forum Discussion

Bian's avatar
Bian
Icon for Helper II rankHelper II
8 years ago
Solved

Cumulative SUM group by date

Hi,   I have a customer table with a date value for when the contract ends. I want to show a column chart with Month on the X axis and a count on customers on Y axis. But the Count needs to be cumu...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Bian,

    You can create the measure using dax below.

    Measure = CALCULATE(DISTINCTCOUNT(CustomerDetail[account]),FILTER(ALL(CustomerDetail),CustomerDetail[Month]<=MAX(CustomerDetail[Month])))




    Regards,
    Lydia

  • Bian's avatar
    Bian
    8 years ago

    Thank you Lydia.

    Managed to find a similiar solution and this is my final measure:

    [Measure] = CALCULATE(DISTINCTCOUNT(CustomerDetail[account]);Account[IsCustomer] = TRUE;filter(all(CustomerDetail[todate])CustomerDetail[todate]<=max(CustomerDetail[todate]));all(DimDate))

     

    Where:

    CustomerDetail is my table for customer deals.

    Account is my customer table

    CustomerDetail[todate] is my date for when the deal ends.

    DimDate is my date table that I use on the X axis on the Chart.

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Bian,

You can create the measure using dax below.

Measure = CALCULATE(DISTINCTCOUNT(CustomerDetail[account]),FILTER(ALL(CustomerDetail),CustomerDetail[Month]<=MAX(CustomerDetail[Month])))




Regards,
Lydia

Bian's avatar
Bian
Icon for Helper II rankHelper II
8 years ago

Thank you Lydia.

Managed to find a similiar solution and this is my final measure:

[Measure] = CALCULATE(DISTINCTCOUNT(CustomerDetail[account]);Account[IsCustomer] = TRUE;filter(all(CustomerDetail[todate])CustomerDetail[todate]<=max(CustomerDetail[todate]));all(DimDate))

 

Where:

CustomerDetail is my table for customer deals.

Account is my customer table

CustomerDetail[todate] is my date for when the deal ends.

DimDate is my date table that I use on the X axis on the Chart.