Forum Discussion

talkprem's avatar
talkprem
Helper I
2 years ago
Solved

Missing Entry Days Count Calculated Column in DAX

Hi, I need a Ageing calculated column when the entry row is blank. if row is blank of entry date side then give number of missing days as I am trying to show row which don’t have data only column...
  • SamInogic's avatar
    SamInogic
    2 years ago

    Hi talkprem ,

    We have used below DAX expression and getting the aging column as 0,1,2 based on the number of days difference from Entry date.

     

    Ageing =
    VAR CurrentDate = Agingtable[Calendar date ]
    VAR EntryDate = Agingtable[entry_date  ]

    // Calculate the last non-blank entry_date before the current row
    VAR LastEntryDate =
        CALCULATE(
            MAX(Agingtable[entry_date  ]),
            FILTER(
                ALL(Agingtable),
                Agingtable[Calendar date ] <= CurrentDate
                    && NOT(ISBLANK(Agingtable[entry_date  ]))
            )
        )

    // Calculate the Ageing based on the difference between current date and last non-blank entry_date
    RETURN
        IF(
            ISBLANK(EntryDate),
            IF(
                NOT(ISBLANK(LastEntryDate)),
                DATEDIFF(LastEntryDate, CurrentDate, DAY),
                BLANK()
            ),
            0
        )

    Please note to make sure you are also considering entry date in column expression, as per your screenshot it is some difference date field.

    Result using expression is as follows,


    Let us know if we are missing anything in the requirement.

    Thanks!

     

talkprem's avatar
talkprem
Helper I
2 years ago

I tried but for me it gave outplut like this

Hi SamInogic Thanks for helping out but i am getting this output in my data.

there are few id columns which may need to added in the && clause. would EARLIER help?
If there was a way to get the last date of entry at the same row of calendar date the it would have helped on the solution.
there are s_id,T_id,P_id, date fields so the calculation should refresh at each new id fields.
the combination of these are unique.
Thank you in advance. and much respect for helping out.

SamInogic's avatar
SamInogic
Super User
2 years ago

Hi talkprem ,

Just to confirm, do you changed the table name and column name as per your data structure. The issue shown in screenshot is due to mismatch of table name and column name.

  • talkprem's avatar
    talkprem
    Helper I
    2 years ago

    Yes I have modified it as per my data structure, Please help

     

    • SamInogic's avatar
      SamInogic
      Super User
      2 years ago

      Hi talkprem ,

      We have used below DAX expression and getting the aging column as 0,1,2 based on the number of days difference from Entry date.

       

      Ageing =
      VAR CurrentDate = Agingtable[Calendar date ]
      VAR EntryDate = Agingtable[entry_date  ]

      // Calculate the last non-blank entry_date before the current row
      VAR LastEntryDate =
          CALCULATE(
              MAX(Agingtable[entry_date  ]),
              FILTER(
                  ALL(Agingtable),
                  Agingtable[Calendar date ] <= CurrentDate
                      && NOT(ISBLANK(Agingtable[entry_date  ]))
              )
          )

      // Calculate the Ageing based on the difference between current date and last non-blank entry_date
      RETURN
          IF(
              ISBLANK(EntryDate),
              IF(
                  NOT(ISBLANK(LastEntryDate)),
                  DATEDIFF(LastEntryDate, CurrentDate, DAY),
                  BLANK()
              ),
              0
          )

      Please note to make sure you are also considering entry date in column expression, as per your screenshot it is some difference date field.

      Result using expression is as follows,


      Let us know if we are missing anything in the requirement.

      Thanks!

       

      • talkprem's avatar
        talkprem
        Helper I
        2 years ago

        The previous logic gave some aging increments but the 2nd one is giving 0 in my dataset.