Help with 12 months cumulative

[AndrejZitnay]

Hello all,

Can you help me with 12 months cumulatives?

I have my data in 3 spredsheets.

I need to do 12 months cumulatives and visual them in one chart over 18 months.

I am using MonthYear = FORMAT(Renewals[Date],"mmm")&"-"&FORMAT(Renewals[Date],"YY") & YearMonthNumber = (YEAR('Renewals'[Date]))*12+MONTH('Renewals'[Date]) for sorting of 18 months chart.

Since data are in 3 tabs I am using  data table :

Date Table 24 months =
FILTER (
    DISTINCT (
        UNION ( VALUES ( Renewals[MonthYear] ), VALUES ( Terminations[MonthYear Retention] ),VALUES(Volumes[MonthYearVolumes] ))
    ),
    ISBLANK ( [MonthYear] ) = FALSE ()
)

I still have problem here with right sort but that is different problem.

All my elements are measurements :

Renewals = CALCULATE(COUNTROWS(Renewals),FILTER(Renewals,Renewals[Type_2]="Renewals"))

Settlements = CALCULATE(COUNTROWS(Renewals),FILTER(Renewals,Renewals[Type_2]="Settlements"))

Measure Renewals related = CALCULATE(COUNTROWS(Terminations),FILTER('Terminations','Terminations'[Renewals?]="Renewals")) this is of retention

New Business = CALCULATE(COUNTROWS(Volumes))

Conquest = [New Business]-[Renewals]

Data are not linked directly hence I have to use Data and Date tables to make relationships on dates and other conditions.

Can you help me?

Sorry for screenshots. I wanted to add here attachment.

Many thanks.

Andrej

[richbenmintz]

@AndrejZitnay

For each month you will need to summarize the sum of your value for the 12 months.

assuming you have a column in your date table that looks like 201709 to define each month of the year you could make a measure like

rolling 12 = calculate([measure], filter(all(datetable), datetable'yyyymm' > max(datetable'yyyymm') -100 && datetable'yyyymm' <= max(datetable'yyyymm')))

hope that helps

[AndrejZitnay]

Hello @richbenmintz,

Thank you for your tip.

That is true. I need to summarize sum for 12 months. For example Jul 17 will have data from Aug 16 - Jul 17

I will try to to that firstly on one spreadsheet.

I will try to do cumulative on 2 measures.

As I date I am using regular Date however I need to show always 2 years view hence I am using :

MonthYear = FORMAT(Renewals[Date],"mmm")&"-"&FORMAT(Renewals[Date],"YY")

to have MMM-YY in Axis to show 2 years view.

I am not sure sure what to add to datateble.

how I should use your measure?

Many thanks in advance.

Kind regards.

Andrej

[Greg_Deckler]

Could you just create an Append or Merge query and append/merge all of the data together?

[AndrejZitnay]

Hello @Greg_Deckler,

What you mean by that?

I was under impressions that Append or Merge query is when I want to sum to spreadsheets.

Thank you.

Andrej

[v-chuncz-msft]

@AndrejZitnay,

You may also consider using UNION Function to add a calculated table.

[AndrejZitnay · Accepted solution]

Hello @v-chuncz-msft,

I might try UNION Function other time.

Now this was my solution =

Last12MCounts = CALCULATE (     [yourmeasure],     DATESBETWEEN (   MasterCalendar[Date],  NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( MasterCalendar[Date] ) ) ),         LASTDATE ( MasterCalendar[Date] )     ) ) 

Thx for tip.

Andrej

[v-chuncz-msft]

@AndrejZitnay,

To help close this thread, you could accept the solution above. Your contribution is highly appreciated.