Rolling 12 months calculation with earliest occuring date

[sharong]

Hi Below is the sample data, [here is the dax I have written, can anyone help me in adding the earliest date condition into this logic]

Rolling sales value = var tables = CALCULATETABLE(data, DATESINPERIOD('Data'[PostingDate], MAX('Data'[PostingDate]), -13, MONTH))

return

CALCULATE([Value], tables)

document no	posting date	sales amount
143	1st june 2022	89
156	4th june 2022	85
143	2nd july 2022	50
132	3rd aug 2022	100
156	5th sep 2022	600
132	5th dec 2022	800
143	1st july 2023	20

So in the calculation,

1) I take only rolling 12 months, for July  2023, [I will consider july 2022 to july 2023]

2) So first two rows will not be considered for calculation

3) And now in remaining rows, document 143 & 132 are repeated. In this case, we have to take the first occuring date for those document numbers.

for document 143, we consider , 2nd July 2022 sales value

for document no 132, we consider, 3rd august 2022 sales value

remaining document values will be same.

So my expected output is:

	Jun-23	Jul-23
Rolling sales value	1074	750

[SiemdeKort]

I created a pbix wich i think solves your question

https://wetransfer.com/downloads/1c3973f6ad7e3bbb7f80dcca5059341e20230823081152/189ee72a7a8d3b07b330...

[sharong]

Hi Thanks, This works. But what will happen if I create a relationship between dimdate and posting date. Will it affect the measure

[SiemdeKort]

Yes, i use the dimdate to calculate a minimum date per month once you make a relation it will return the posting dates. so the dimdate needs to stay without a relation.

Can you accept this as a solution?
Connect on: Linkedin

[Mahesh0016]

@sharong 

	Jun-23	Jul-23
Rolling sales value	1074	750

Please Can you Elaborate above table out how can Jun -23 = 1074 calculated. Thank You.

[sharong]

Sorry, Its my mistake i have written 1074

For Jun 2023, again we consider rolling 12 months [which is jun 2022 to jun 2023] in the calculation.

So, 89 + 85 + 100 = 274 

document no	posting date	sales amount
143	1st june 2022	89
156	4th june 2022	85
143	2nd july 2022	50
132	3rd aug 2022	100
156	5th sep 2022	600
132	5th dec 2022	800
143	1st july 2023	20