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

Frequent Visitor

## Calculation to start after skipping 12 months and then rolling sum

Hi all,

Out data range is for 48 months. We are trying to calculate the running total that should start  from 13th month onward considering last 12 months. And for 14th month (next year) this should have Month 2 to Month 13, so on and so forth. A small excel snippet is below. Can we do this in DAX? Much appreciate your inputs.

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Frequent Visitor

Thank you @Ashish_Mathur It worked perfect for me. The only change I did was to include a serial number  ([SNO]) column which was giving right results versus the "Months transpired"

SNO = RANKX(ALLSELECTED(Table),CALCULATE(MAX(Table[Date Key])),,DESC,Dense)
---------------

12 months running total=
IF ([SNO]>=12, CALCULATE(TableName[Measure],DATESBETWEEN('Date'[Date],MIN('Date'[Date]),EDATE(MAX('Date'[Date]),11))),BLANK())

Thanks to everyone for investing their valuable time!

Frequent Visitor

@Jihwan_Kim this is the data I was looking to solve where I do not have a Month No. since the date could start from anywhere, Thanks a ton.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

If you could provide the output that you expected or the sample pbix files(without sensitive data) would be great.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Also, you may check if these thread helps

Solved: Rolling sum next 12 months of sales - Microsoft Power BI Community

Solved: Rolling 12 Month Calculation (Not Cumalative) - Microsoft Power BI Community

Solved: PY rolling 12 months calculation issue - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

Frequent Visitor

Thank you for the response @v-xiaoyan-msft . here is my dataset.

1.My Annual Value starts acukmulating after 12 months always and for each row it will be calculated for the past 12 months.

2.The below values are from the transaction table which has only one row per each month.
3. I have a Date table to join with the Transaction table as well.

4. Source is SAP HANA using DirectQuery
I tried with DATESINPERIOD but always returns the same number as the "Value" in each row

Date, Value, Annual Value

10/21/2015, 12
9/21/2015, 16
8/20/2015, 24
7/21/2015, 29
6/19/2015, 35
5/20/2015, 41
4/21/2015, 47
3/23/2015, 53
2/20/2015, 59
1/21/2015, 65
12/19/2014, 71
11/19/2014, 77, 532
10/21/2014, 83, 603
9/22/2014, 89, 677
8/21/2014, 95, 748
7/22/2014, 101, 820
6/20/2014, 107, 892
5/21/2014, 113, 964
4/21/2014, 119, 1,036
3/21/2014, 125, 1,108
2/20/2014, 131, 1,180
1/21/2014, 137, 1,252
12/19/2013, 143, 1,324
11/19/2013, 149, 1,396