cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Rolling 12 months calculation with earliest occuring date

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
5 REPLIES 5
Frequent Visitor
Helper I

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

Frequent Visitor

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?

Super User
 Jun-23 Jul-23 Rolling sales value 1074 750

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

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors