cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper II

## How to calculate Running totals for a constant value/month?

Hi friends,

I would like your suggestion on calculating running totals for a given value/ month.

Currently, I am calculating running totals for expected usage/month (i.e. 75000/month).

I am trying to calculate running expected totals for my data in the data table(Oct-14 to Oct-17) where I came up with the below measure:

Expected Total = COUNTROWS (
SUMMARIZE (Records, Records[Date].[Year], Records[Date].[Month]))*75000 ,  which is giving me running total as 75000*37 months =2775000 (i.e. from27-Oct-14 to 26-Oct-17).

Instead I want my measure to calculate the expected running totals as 75000*36 months = 270000.

How would I be able to modify my measure to calculate running toatals for 36 months?

Kindly help!!!

Thank you.

2 REPLIES 2
Employee

Hi @Rck7,

This could be a trick. The intervals from 27-Oct-14 to 26-Oct-17 are 36 months. But the records of month are 37 when you used COUNTROWS. Because October is counted one time more.

For example, from 2016-01-27 to 2017-01-26. There are 12 months intervals but 13 months of records. 2016-01 to 2016-12 and 2017-01.

It seems you don't need concern the context. Maybe you can minus 1 directly. Or use function DATEDIFF instead.

```DateDiff =
DATEDIFF ( DATE ( 2014, 10, 27 ), DATE ( 2017, 10, 26 ), MONTH )```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

Hi,

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors