Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aktelidevara
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.

aktelidevara_0-1663870368446.pngaktelidevara_1-1663870390016.png

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
aktelidevara
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!




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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xiaoyan-msft
Community Support
Community Support

Hi @aktelidevara ,

 

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

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

aktelidevara_0-1663970225086.png

 





Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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