Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
@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
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
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
10 | |
10 | |
9 | |
6 |