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

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors