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!

     

SamInogic's avatar
SamInogic
Super User
2 years ago

Hi talkprem 

You can use below DAX query to create calculated column "Aging",

Ageing =
VAR CurrentDate = Agingtable[Calendar date ]
VAR EntryDate = Agingtable[entry_date  ]
RETURN
    IF(
        ISBLANK(EntryDate),
        DATEDIFF(
            CALCULATE(
                MAX(Agingtable[entry_date  ]),
                FILTER(
                    Agingtable,
                    Agingtable[Calendar date ]< CurrentDate
                        && NOT(ISBLANK(Agingtable[entry_date  ]))
                )
            ),
            CurrentDate,
            DAY
        ),
        0
    )

Result:

 

Thanks!

 

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at [email protected]
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
talkprem's avatar
talkprem
Helper I
2 years ago

Thanks SamInogic  for helping me ,
I Tried this solution but this does not calculate the actual gap between the entrydate and calendar date, this only shows 1 for each missing entry row. instead of showing 1 and 2 between 16/6 to 18/6 this is showing 1 and 1 for 16/6 to 18/6
can you please help me? maybe earlier function might be needed but i am unable to work it out.

  • SamInogic's avatar
    SamInogic
    Super User
    2 years ago

    Hi talkprem ,

    Then below DAX expression might help you to get the expected result,

    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
        )


    Thanks!

     

    Inogic Professional Services: Power Platform/Dynamics 365 CRM
    An expert technical extension for your techno-functional business needs
    Drop an email at [email protected]
    Service: https://www.inogic.com/services/
    Tips and Tricks: https://www.inogic.com/blog/