Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Dynamic Aging of open items

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.

Sandor1984_3-1597836031239.png

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.

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

This recipe

deal date weeknum < calendar weeknum < settlement weeknum

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

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

Sandor1984_0-1598363272252.png

 

Sandor1984_1-1598363335414.png

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.