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.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

If you had a separate Month table, you could use that as your axis and then simply do a SUM with a CALCULATE and FILTER the fact table to Fact[Month] <= Date[Month], something along those lines.

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

    Came up with the following formula based on your suggestion.

    Cumulative =CALCULATE(DISTINCTCOUNT(CustomerDetail[account]);all(CustomerDetail);FILTER(CustomerDetail;CustomerDetail[todate]<=RELATED(dimdate[date])))

     

    The result is the same as without the filter.

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Perhaps remove your ALL clause or structure it this way:

       

      Cumulative =CALCULATE(DISTINCTCOUNT(CustomerDetail[account]);FILTER(all(CustomerDetail);CustomerDetail[todate]<=RELATED(dimdate[date])))