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