cancel
Showing results for
Did you mean:
Resolver I

## Calculate be 12 months or a rollout by month

On the following table "QuoteConvertionStartDate" I have columns for Date, Total, and Accepted

I need to calculate the "Total 12 moth roll" and "Accepted 12 Moth Roll".

I did the numbers manually to give a result.

For example to calculate the Total 12 months roll for Tusday January 1 2019 I have to sum:

Tusday January 1 2019 to Thrusday February 1 2018 = 40

To calculate the Total 12 months roll for Saturday December 1 2018 I have to sum:

Saturday December 1 2018 to Monday January 1, 2018 = 41

To calculate the Total 12 months roll for Thusday November 1 2018 I have to sum:

Thusday November 1 2018 to Friday December 1, 2017 = 45

and so on.

I have to do the same for Accepted.

I tried the following DAX but is giving me the total by month and is giving me the total by 12 months or rolling back 12 months:

__Value L12M =

VAR __EndDate = EOMONTH(LASTDATE(QuoteConvertionStartDate[Date].[Date]),0)

VAR __StartDate = DATE(YEAR(__EndDate),MONTH(__EndDate) - 12, 2)

RETURN

CALCULATE(QuoteConvertionStartDate[Total], DATESBETWEEN(QuoteConvertionStartDate[Date].[Date], __StartDate, __EndDate))

Thanks

2 ACCEPTED SOLUTIONS
Resolver I

Thank you for the help.

This solution works but only if I select the year and the month.

I am using Power KPI 2.0 to show the rolling average and date hierarchy only shows the year. This gives me a really bad chart.

I am new at Power BI and I don't know what to do to fix this problem.

Resolver I

I fixed the problem with this solution

3 REPLIES 3

You need a calendar table to write time intelligence formulas

here are some articles that I wrote

https://exceleratorbi.com.au/power-pivot-calendar-tables/

https://exceleratorbi.com.au/dax-time-intelligence-beginners/

you could write something like

CALCULATE(sum(table[column]),datesinperiod(calendar[date],max(calendar[date]),-1,year))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Resolver I

Thank you for the help.

This solution works but only if I select the year and the month.

I am using Power KPI 2.0 to show the rolling average and date hierarchy only shows the year. This gives me a really bad chart.

I am new at Power BI and I don't know what to do to fix this problem.

Resolver I

I fixed the problem with this solution

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors