Forum Discussion
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 cumulative so I can show all active customers on that part in time.
Cant seem to get my head around this problem som hoping for your help.
This formula is as far as i got:
Cumulative = CALCULATE(DISTINCTCOUNT(CustomerDetail[account]);all(CustomerDetail))
Thank you and please let me know if you need more info.
Bian,
You can create the measure using dax below.Measure = CALCULATE(DISTINCTCOUNT(CustomerDetail[account]),FILTER(ALL(CustomerDetail),CustomerDetail[Month]<=MAX(CustomerDetail[Month])))
Regards,
LydiaThank 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.
5 Replies
Bian,
You can create the measure using dax below.Measure = CALCULATE(DISTINCTCOUNT(CustomerDetail[account]),FILTER(ALL(CustomerDetail),CustomerDetail[Month]<=MAX(CustomerDetail[Month])))
Regards,
LydiaThank 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.
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.
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.
Perhaps remove your ALL clause or structure it this way:
Cumulative =CALCULATE(DISTINCTCOUNT(CustomerDetail[account]);FILTER(all(CustomerDetail);CustomerDetail[todate]<=RELATED(dimdate[date])))
