Sum value based on Current Month

[dla005]

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

[parry2k]

@dla005 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-ca...

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.⚡

[dla005]

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