topic Re: Rolling 12 months calculation with earliest occuring date in DAX Commands and Tips

Rolling 12 months calculation with earliest occuring date

sharong — Wed, 23 Aug 2023 05:01:22 GMT

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

Re: Rolling 12 months calculation with earliest occuring date

Mahesh0016 — Wed, 23 Aug 2023 07:24:26 GMT

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.

Re: Rolling 12 months calculation with earliest occuring date

SiemdeKort — Wed, 23 Aug 2023 08:13:51 GMT

I created a pbix wich i think solves your question

https://wetransfer.com/downloads/1c3973f6ad7e3bbb7f80dcca5059341e20230823081152/189ee72a7a8d3b07b3302cb68a743f6420230823081213/cf994c

Re: Rolling 12 months calculation with earliest occuring date

sharong — Wed, 23 Aug 2023 09:24:01 GMT

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

Re: Rolling 12 months calculation with earliest occuring date

sharong — Wed, 23 Aug 2023 09:59:10 GMT

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

Re: Rolling 12 months calculation with earliest occuring date

SiemdeKort — Wed, 23 Aug 2023 10:23:32 GMT

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