topic Re: Dynamic Aging of open items in DAX Commands and Tips

Dynamic Aging of open items

Anonymous — Wed, 19 Aug 2020 11:30:14 GMT

Dear All!

I would like to ask for your help as I am strugling "translating" an excel formula into dax.

On the left is the data table. On the right (in yellow) is the desired result.

Excel formula I a using here is:

+SUMIFS(Table1[Amount, EUR];
Table1[Deal Date week num];"<="&M5;
Table1[Settlement week num];">="&M5)

What I am trying to solve is, what was open/closed status of the items in the past.

What is was open 3 weeks ago, was closed 2 weeks ago. etc.

There are 3 different types of weeknumber: calendar week num, deal date weeknum, settlement weeknum.

The items is defined open when: deal date weeknum < calendar weeknum < settlement weeknum. (as the excel formula is showing above.

Thank you in advance for your help

Best regards

Re: Dynamic Aging of open items

amitchandak — Wed, 19 Aug 2020 11:54:55 GMT

Anonymous , Hope you already have week number (calendar week) Table

Try like

CALCULATE(Sumx(FILTER(Table,Table[Seal Deal week Date]<=max('Week'[Week]) && ( Table[Settlement week num]>max('Week'[Week]))),(Table[Amount EUR])))

Re: Dynamic Aging of open items

Anonymous — Wed, 19 Aug 2020 14:36:22 GMT

This recipe

deal date weeknum < calendar weeknum < settlement weeknum

is not what your Excel formula calculates. Your formula uses the "<=" relational operator.

Re: Dynamic Aging of open items

Anonymous — Tue, 25 Aug 2020 13:50:12 GMT

Hello amitchandak !

For the first try I got an error.

Yes, I have a separate calendar week number. In order to have the dax working, between which columns do you suggest to have the relationship?

If I make a relationship between Calendar Week date and Deal date , thank the result is the sum of deals on the selected week only. How can I make it a cummulative number?

As I see the result, the dax is not taking into consideration both conditions. Any idea, what mistake did I make?

Below you can see the reationship and the syntax too.

Thank you for your help in advance

Re: Dynamic Aging of open items

Anonymous — Tue, 25 Aug 2020 18:59:06 GMT

Hi!

In the meantime I solved the problem.

Modified your syntax a bit:

Suggestion:

CALCULATE(Sumx(FILTER(Table,Table[Seal Deal week Date]<=max('Week'[Week]) && ( Table[Settlement week num]>max('Week'[Week]))),(Table[Amount EUR])))

Final one:

CALCULATE(Sumx(FILTER(allselected(Table),Table[Seal Deal week Date]<=max('Week'[Week]) && ( Table[Settlement week num]>max('Week'[Week]))),(Table[Amount EUR])))

Thank you