Forum Discussion

BudMan512's avatar
BudMan512
Helper V
4 years ago
Solved

Net Customer Gain-Loss

I have a table that includes Customer Id, Start Date and End Date.

I have created pages of visuals, one for customer gains and the other for customer losses.

I am using slicers for year and month to determine the counts.

I am trying to create a 3rd page that would summarize the gains and losses.

I am having no luck figuring this out.  I would like to have one card with Gains, one with losses and one for the Net customer counts.

Any assistance would be greatly appreciated.

thanks

Need Help
  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    4 years ago
    Net Gain/Loss = COUNT('Customer'[START_DATE]) - COUNT('Customer'[End_Date])

     

    You get these "by year" by using years in the x-axis. I hope you are using a separate calendar table.

    I deeply recommend some power bi tutorials through ms learning paths.

3 Replies

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Continued Contributor
    4 years ago

    What are your measures for gain and losses?

     

    Should be as simple as net = gain - losses

    • BudMan512's avatar
      BudMan512
      Helper V
      4 years ago

      Thanks for the reply Gabriel,

      I didn't use measures for the Gains and Losses pages but rather visuals and filters.  I did use the below measure for the Net.  I found this DAX elsewhere on the Forum.

       

      Net Gain/Loss = 
 var _DistinctCount_Gain_by_Year = Calculate(DISTINCTCOUNT('Customer'[Cust_ID]),FILTER('Customer','Customer'[START_DATE]="StartDate"))
 var _DistinctCount_Loss_by_year = CALCULATE(DISTINCTCOUNT('Customer'[CUST_ID]),Filter('Customer','Customer'[End_Date]="LossDate"))
RETURN
 _DistinctCount_Gain_by_year -_DistinctCount_Loss_by_year

       It is returning this error

      Thanks

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Continued Contributor
    4 years ago
    Net Gain/Loss = COUNT('Customer'[START_DATE]) - COUNT('Customer'[End_Date])

     

    You get these "by year" by using years in the x-axis. I hope you are using a separate calendar table.

    I deeply recommend some power bi tutorials through ms learning paths.