Forum Discussion
Sum value based on Current Month
Hi,
I am trying build a Dax for starting inventory for the month(all open inventory from prior months). However, I cant seem to be able to create the dax for it.
So my goal is i want to capture all the received date that has a paid date and blank up to up to 1 month before the date on the date table (For example if the date table is May 20 then i want all th recieved date up to April)
I tried using this create column DAX: IF(Recieved date = Complete Date),0,1) however, in May it does not include claim 2 since the recieved date is in 202003 and the complete date is 202005. Is there a way around this? Thank you!!
Below is my fake data and the table i am tryihng to create.
Goal
| Date table | Count | |
| Received date | Mar-20 | |
| Received date | Apr-20 | 4 |
| Received date | May-20 | 5 |
Data
| Claim | Received date | Complete date | Apr | May | |
| 1 | 202003 | 202004 | 1 | ||
| 2 | 202003 | 202005 | 1 | 1 | |
| 3 | 202003 | 202003 | 0 | ||
| 4 | 202003 | 1 | 1 | ||
| 5 | 202003 | 202005 | 1 | 1 | |
| 6 | 202004 | 202005 | 0 | 1 | |
| 7 | 202004 | 0 | 1 | ||
| 8 | 202004 | 202004 | 0 | ||
| 9 | 202005 | 0 | 0 | ||
| 10 | 202005 | 0 | 0 | ||
| 11 | 202005 | 202005 | 0 | 0 |
2 Replies
Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/and then use Openingbalancemonth DAX function.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Weird I think my message got deleted while I was editing the table to make it look more readable.
parry2k I made the 2 changes per your recommendation and i got really close to the answer. The result I got from the OpeningBalanceMonth(Opening BalanceMonth(Sumx(query1,query[value], date[Month])
Mar - 26,850
Apr - 24,147
May - 20,109
However, it does not account for prior month value that are still open. For example, May should be 22,106( 20,109+46+37+1,828 +46) and April should be 25,443 (24,147+1,233+46+37). Is there a way i can edit the DAX to account for the prior months that was not completed till later? Thank you so much for your help
Complete date <3/1/2020 Mar Apr May Jun Total Date Recieved Feb 0 25,534 1,233 46 37 26,850 Mar 0 0 22,253 1,828 46 24,127 Apr 409 0 0 18,339 1,361 20,109
