Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
Proud to be a Super User!
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
Could you just create an Append or Merge query and append/merge all of the data together?
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
You may also consider using UNION Function to add a calculated table.
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
To help close this thread, you could accept the solution above. Your contribution is highly appreciated.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |