Forum Discussion

JCK2's avatar
JCK2
Helper III
6 years ago
Solved

Dax column calculation

Hello,   i want to create a measure to arrive at one number as how much is due in the month, I wrote teh below calculation in column, however this is not helping me achive what i want.   How does...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

JCK2 OK, trying to come back up to speed on this, what are your formulas for the three columns?

JCK2's avatar
JCK2
Helper III
6 years ago

Greg_Deckler 

 

These are the calculations i am using...

Audits due column = CALCULATE(DISTINCTCOUNT(' Audits'[PRID]),VALUES(' Audits'[Compliance Date]))

 

 

Audits Over due Column = IF((' Audits'[Compliance Date]< ' Audits'[Date Audit Performed])||  ISBLANK(' Audits'[Date Audit Performed]) && (' Audits'[Compliance Date])< TODAY(),1,0)

 

Thanks for checking Greg!!

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    OK, well here is your circular dependency when you use these together:

     

    Audits due column = CALCULATE(DISTINCTCOUNT(' Audits'[PRID]),VALUES(' Audits'[Compliance Date]))

     

    Audits Over due Column = IF((' Audits'[Compliance Date]< ' Audits'[Date Audit Performed]) ||  ISBLANK(' Audits'[Date Audit Performed]) && (' Audits'[Compliance Date])< TODAY(),1,0)

     

    So, I don't understand that if the 2 things above are columns, why are you using VALUES? Are these measures or columns in a table?

     

    Really, some SAMPLE data would be incredibly useful. Just make some up that emulates your actual data. Would only need 2 or 3 rows.

    • JCK2's avatar
      JCK2
      Helper III
      6 years ago

      Greg_Deckler , I've created a sample data set, also tried to explain as i can!

       

      Hope its clear, please let me know if you need more info! Thanks a ton 🙂

       

      Sorry here is the link to https://www.dropbox.com/sh/w1cxxiijhn7060u/AACtywXqUK00HuAKWRP2vstYa?dl=0 

       

       

       

      IdSiteCompliance DateDate PerformedTotal DueTotal Overdue% performed on time
      1Amsterdam10/03/202009/03/202010100.00%
      2Utrecht12/03/202010/03/202010100.00%
      3Venlo12/03/202010/03/202010100.00%
      4Amsterdam13/03/202015/03/2020110.00%
      5Lellyan13/03/2020 11 ( over due because not performed)0.00%
      6Milan17/03/202017/03/202010100.00%
      7Rome 12/03/2020Not counted because there is no complaince date #VALUE!
      8Spain13/03/202014/03/202010100.00%
      9Utrecht13/03/202015/03/2020110.00%
      10Amsterdam15/03/202015/03/202010100.00%
      11Lisbon15/03/202015/03/202010100.00%
      12Milan22/03/202022/03/2020Not counted because the date is in future #VALUE!
             
      Total   10370.00%
             
        Total Due: Countdistint of id where compliance date is non blank and less than today (ID is unique and there can be multiple audits on same date), so need to take id into consideration for total due
             
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        OK, I did it with these two columns:

         

        Total Due 1 = IF(ISBLANK([Compliance Date]) || [Compliance Date] > TODAY(),BLANK(),1)

Total Overdue 1 = 
    SWITCH(TRUE(),
        ISBLANK([Compliance Date]),BLANK(),
        ISBLANK([Date Performed]) && [Compliance Date] < TODAY(),1,
        [Date Performed] > [Compliance Date],1,
        [Compliance Date]>TODAY(),BLANK(),
        0
    )

         

        And this measure: 

        Measure = 
    DIVIDE(COUNTROWS(FILTER('Table',NOT(ISBLANK([Total Overdue 1])) && [Total Overdue 1] = 0)),SUM('Table'[Total Due 1]))

        PBIX is attached. I think you have an error in your data for Spain. Spain looks like it is overdue.