topic Re: Calculate be 12 months or a rollout by month in DAX Commands and Tips

Calculate be 12 months or a rollout by month

TiaCamilian — Fri, 25 Oct 2019 20:34:48 GMT

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))

Can someone help please.

Thanks

MattAllington — Sat, 26 Oct 2019 03:46:05 GMT

You need a calendar table to write time intelligence formulas

here are some articles that I wrote

you could write something like

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

TiaCamilian — Wed, 30 Oct 2019 14:10:06 GMT

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.

TiaCamilian — Mon, 04 Nov 2019 13:33:43 GMT

I fixed the problem with this solution