Dynamic Aging of open items

[Anonymous]

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

S.

[Anonymous]

This recipe

deal date weeknum < calendar weeknum < settlement weeknum

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

[amitchandak]

@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])))

Similar solution : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

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

S

[Anonymous · Accepted solution]

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

S