Forum Discussion

JPux's avatar
JPux
Frequent Visitor
2 years ago
Solved

Counting and substracting by date

Hi all, Tx in advance for reading this. For sure a very basic question for most of you, but I've searched the forum for similar entries and I couldn't find what I needed. Apologies in advance if ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, JPux 

    You can try following DAX to solve the problem, however, the fourth Total graph needs to be used in conjunction with the NCA - Churn chart, which you can use at your discretion.

     


    Here are the steps to make a Total line chart.
    Step 1:
    Right clock the third NCA - Churn chart, if you have not the New calculation, you can open Visual calculation in Preview features in Options and Settings, and then reopen the pbix file.

     



    Step 2:
    Right clock the third NCA - Churn chart, and choose New calculation, clock Fx and choose Running sum, then replace the Filed with NCA - Churn.



    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, JPux 

I think you should rethink the logic of implementing the third and fourth images. For example, if the X-axis of NCA is based on NCA Date and the X-axis of Churn is based on Churn Date, then it is not clear which column the X-axis of the third image is based on; on the image it looks like the row values of both are directly subtracted from each other, but the columns that the X-axis is based on are not the same, and a direct subtraction won't give you the answer that you are looking for. The image-based representation is a bit abstract, so I don't know if you'll get the point. I will also try to find a solution to help you solve the problem as soon as possible.

Best Regards,
Yang
Community Support Team

JPux's avatar
JPux
Frequent Visitor
2 years ago

Hi Ynag (Anonymous), tx for your reply.

I've edited my initial post and added the rationale for the calculations. Hope it brings more clarity.

Thanks a lot for your help.

J

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, JPux 

    You can try following DAX to solve the problem, however, the fourth Total graph needs to be used in conjunction with the NCA - Churn chart, which you can use at your discretion.

     


    Here are the steps to make a Total line chart.
    Step 1:
    Right clock the third NCA - Churn chart, if you have not the New calculation, you can open Visual calculation in Preview features in Options and Settings, and then reopen the pbix file.

     



    Step 2:
    Right clock the third NCA - Churn chart, and choose New calculation, clock Fx and choose Running sum, then replace the Filed with NCA - Churn.



    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • JPux's avatar
      JPux
      Frequent Visitor
      2 years ago

      Hi Yang ( Anonymous )

       

      Thanks so much for your reply.

      Seeing how you created the "NCA-Churn" chart, has helped me to clarify some concepts I had misunderstood.

       

      And I figured out how to do the Running Total in a separate chart. FYI.

       

      (File with Dashboard v5)

       

      - I've created two new columns in the main "DataSourceTest" table, as follows:

       

       

       

      Churn YYYYMM = (YEAR([Churn Date])*100)+ MONTH([Churn Date])

NCA YYYYMM = (YEAR([NCA Date])*100)+ MONTH([NCA Date])

       

       

       

       

      - In the "CalendarMaster" table, I've created another column following this same logic:

       

       

       

      MasterDate YYYYMM = (YEAR([Date])*100)+ MONTH([Date])

       

       

       

       

      - And then I've made this meassure for the Running Total:

       

       

       

      CustomersRunningTotal = 
VAR _Month = SELECTEDVALUE('CalendarMaster'[MasterDate MM_YYYY])
VAR _MonthNum = YEAR(_Month)*100 + MONTH(_Month)
VAR _TodayNum = YEAR(TODAY())*100 + MONTH(TODAY())

VAR _NCAtoDate = CALCULATE(COUNTROWS('DataSourceTest'),'DataSourceTest'[NCA YYYYMM]<=_MonthNum)
VAR _ChurntoDate = CALCULATE(COUNTROWS('DataSourceTest'), 'DataSourceTest'[Churn YYYYMM]<=_MonthNum && 'DataSourceTest'[Churn YYYYMM]<> BLANK())

VAR _RunningTotal = if(_MonthNum <= _TodayNum, _NCAtoDate - _ChurntoDate,BLANK())
RETURN _RunningTotal

       

       

       

      (The _TodayNum is to stop the graph from displaying values beyond the current month.)

       

      With all the above, building the visual is quite easy:

       

       

      I mark your post as the "Accepted Solution" because I've learnt more by analyzing the pbix file in your post above than with dozens of articles.

       

      Thanks a lot for your help.

      J