cancel
Showing results for
Did you mean:
Highlighted
Helper V

## Help with 12 months cumulative

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper V

## Re: Help with 12 months cumulative

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

7 REPLIES 7
Highlighted
Super User IV

## Re: Help with 12 months cumulative

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Solution Sage

## Re: Help with 12 months cumulative

@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

Highlighted
Helper V

## Re: Help with 12 months cumulative

Hello @richbenmintz,

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?

Kind regards.

Andrej

Highlighted
Helper V

## Re: Help with 12 months cumulative

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

Highlighted
Community Support

## Re: Help with 12 months cumulative

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper V

## Re: Help with 12 months cumulative

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

Highlighted
Community Support

## Re: Help with 12 months cumulative

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

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors