cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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
columns till pu_no are master data columns, from column Entry_date to PuNo are readings data
datediff between Entry_date previous row and calendar date current row might work
as it needs to reset once data comes in

 Calendar date id T_no pu_no entry_date SL id S Name OBBB Pr Name PuNo Ageing 16/6/2024 10023 1 17 16/6/2024 aaa 10023 site1 er56 material1 17 0 17/6/2024 10023 1 17 1 18/6/2024 10023 1 17 2 19/6/2024 10023 1 17 19/6/2024 aaa 10023 site1 er56 material1 17 0 20/6/2024 10023 1 17 1 21/6/2024 10023 1 17 21/6/2024 aaa 10023 site1 er56 material1 17 0 22/6/2024 10023 1 17 22/6/2024 aaa 10023 site1 er56 material1 17 0 23/6/2024 10023 1 17 1 24/6/2024 10023 1 17 24/6/2024 aaa 10023 site1 er56 material1 17 0

Any Help would be Appreciated.

@

1 ACCEPTED SOLUTION
Super User

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!

Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
8 REPLIES 8
Super User

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
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Helper I

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.

Super User

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.

Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Helper I

Super User

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!

Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Helper I

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

Helper I

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.

Super User

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
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.