Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chinoos
Helper I
Helper I

Rolling 12 month sum for completed months

I'm struggling to get rolling 12 months for the months are completed. For example; For the current month the value should display Zero and for the completed month it should display rolling 12 months values. For 2016-Dec the value=sum(2016-Jan till 2016-Dec), 2016-Nov=sum(2015-Dec till 2016-Nov)

 

YearMonthValue
20168985193929.9
20169985428542.2
201610985365989.1
201611992519096.7
201612980109375.8
201710

 

Please suggest any idea, if you have come across the same

4 ACCEPTED SOLUTIONS
Vvelarde
Community Champion
Community Champion

@Chinoos

 

you can check if a month is complete with: (Bold Part)

 

ValueCalc = if(TODAY()>=EOMONTH(VALUES(Table1[DateRef]);0);EXPRTRUE;EXPRFALSE)




Lima - Peru

View solution in original post

v-shex-msft
Community Support
Community Support

Hi @Chinoos,

 

According to your description, you can refer to below formulas if it works on your side.

 

1. Calculate the total value from previous date to current date.

 

Total = 
var minDate= DATE(YEAR(MAX([Date])),MONTH(MAX([Date]))-11,DAY(MAX([Date])))
return 
SUMX(FILTER(ALL('Table'),[Date]>= minDate&&[Date]<=MAX([Date])),[Amount])

 

2. Calculate the rolling total in current year.

 

Rolling total = SUMX(FILTER(ALL('Table'),[Date]<=MAX([Date])&&[Date].[Year]=MAX([Date].[Year])),[Total])

 

 

If above is not help, please share some sample data to test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Thanks the formulas, it worked for rolling 12 months sum, but i had mades some changes since date and measure are coming from 2 different tables. I'm using just one formula to get the result.

 

Rolling 12 months =
var minDate= DATE(YEAR(MAX('Fiscal Calendar Date'[Fiscal Calendar Date])),MONTH(MAX('Fiscal Calendar Date'[Fiscal Calendar Date]))-12,DAY(MAX('Fiscal Calendar Date'[Fiscal Calendar Date])))
return
CALCULATE( SUM ( 'Sales'[Gross Sales] ),FILTER(ALL('Fiscal Calendar Date'),'Fiscal Calendar Date'[Fiscal Calendar Date]> minDate&&'Fiscal Calendar Date'[Fiscal Calendar Date]<=MAX('Fiscal Calendar Date'[Fiscal Calendar Date])))

 

Please find the sample date below( from the Fiscal Calendar Date Hierarchy, I'm dsiplaying only the Year and Month)

YearMonthGross
2015January55039455.96
2015February49964844.34
2015March67875264.74
2015April85248808.27
2015May88028313.78
2015June97662930.16
2015July93672919.35
2015August89597379.02
2015September93686553.51
2015October97872548.47
2015November81438568.72
2015December77867879.91
2016January55328963.33
2016February61547754.60
2016March78241869.13
2016April81956103.71
2016May89760083.71
2016June96347579.89
2016July83884792.87
2016August97057881.05
2016September95230929.74
2016October90063862.46
2016November90997731.83
2016December59691823.46

View solution in original post

As mentioned earlier, this is actually better suited for DAX, assuming that you've got a full data model in place (eg, a date table). Power Query (ie 'M') has performance issues - for example, if your dataset has a lot of columns in it, then it will run much slower than one that has just a few columns). So you might treat this as an exercise in getting familiar with what is possible in M, even if it's not the best approach.

 

In DAX, you might try something like this - 
using the recommended idea of having building blocks of measures, create a measure for total net sales:
TotalNetSales = SUM( [NETSALES] )

Then a measure for rolling 12month sales:
12MonthRollingSales =
CALCULATE([TotalNetSales],
DATESINPERIOD(DimDates[Date],
LASTDATE(DimDates[Date]),-12, Month
)
)

... that's of course assuming that the fact table is connected to a proper date table (DimDates in my example, where [Date] is every calendar date - provided that MONTH_FISCAL is a proper date, then it can be linked to the date table)

View solution in original post

17 REPLIES 17
giyaq1688
New Member

I am able to use the formula to calculate the rolling 12 months sum, but when I try to include monthly data and compare it side by side.  The monthly data shows a strange number.

 

Anyone know the reason? 

giyaq1688
New Member

I am able to use the formula to calculate the rolling 12 months sum, but when I try to include monthly data and compare it side by side.  The monthly data shows a strange number.

 

Anyone know the reason? 

v-shex-msft
Community Support
Community Support

Hi @Chinoos,

 

According to your description, you can refer to below formulas if it works on your side.

 

1. Calculate the total value from previous date to current date.

 

Total = 
var minDate= DATE(YEAR(MAX([Date])),MONTH(MAX([Date]))-11,DAY(MAX([Date])))
return 
SUMX(FILTER(ALL('Table'),[Date]>= minDate&&[Date]<=MAX([Date])),[Amount])

 

2. Calculate the rolling total in current year.

 

Rolling total = SUMX(FILTER(ALL('Table'),[Date]<=MAX([Date])&&[Date].[Year]=MAX([Date].[Year])),[Total])

 

 

If above is not help, please share some sample data to test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

 I am trying to get the total sales for the past 12 months. The error message I get is Filter is not supported in this context in Direct query mode

 

Perioddate is derived from a sliver for Month / Year Selected, so if 2017 / 08 is selected perioddate = 01/08/2017

Sales is the sales number for each record in the table and I want this total from the dale slicer - 12months from perioddate

 

StockSales =
var minDate= DATE(YEAR(MAX([perioddate])),MONTH(MAX([perioddate]))-11,DAY(MAX([perioddate])))
return
SUMX(FILTER(ALL('EOM_STK'),[perioddate]>= minDate&&[perioddate]<=MAX([perioddate])),[Sales])

 

Any ideas how to calculate the 12 months sales from the slicer date ?

 

 

Hi

can you please advise how to fill this out.

 

my sheet if called Tech Prod, i have rows Year, Month, Store Name and Tech Producivity.

I am unclear how to what to fill in for the [Date} fields.

'Total = 'Tech Prod'
var minDate= DATE(YEAR(MAX([Date])),MONTH(MAX([Date]))-11,DAY(MAX([Date])))

 

Thanks

Thanks the formulas, it worked for rolling 12 months sum, but i had mades some changes since date and measure are coming from 2 different tables. I'm using just one formula to get the result.

 

Rolling 12 months =
var minDate= DATE(YEAR(MAX('Fiscal Calendar Date'[Fiscal Calendar Date])),MONTH(MAX('Fiscal Calendar Date'[Fiscal Calendar Date]))-12,DAY(MAX('Fiscal Calendar Date'[Fiscal Calendar Date])))
return
CALCULATE( SUM ( 'Sales'[Gross Sales] ),FILTER(ALL('Fiscal Calendar Date'),'Fiscal Calendar Date'[Fiscal Calendar Date]> minDate&&'Fiscal Calendar Date'[Fiscal Calendar Date]<=MAX('Fiscal Calendar Date'[Fiscal Calendar Date])))

 

Please find the sample date below( from the Fiscal Calendar Date Hierarchy, I'm dsiplaying only the Year and Month)

YearMonthGross
2015January55039455.96
2015February49964844.34
2015March67875264.74
2015April85248808.27
2015May88028313.78
2015June97662930.16
2015July93672919.35
2015August89597379.02
2015September93686553.51
2015October97872548.47
2015November81438568.72
2015December77867879.91
2016January55328963.33
2016February61547754.60
2016March78241869.13
2016April81956103.71
2016May89760083.71
2016June96347579.89
2016July83884792.87
2016August97057881.05
2016September95230929.74
2016October90063862.46
2016November90997731.83
2016December59691823.46
Vvelarde
Community Champion
Community Champion

@Chinoos

 

you can check if a month is complete with: (Bold Part)

 

ValueCalc = if(TODAY()>=EOMONTH(VALUES(Table1[DateRef]);0);EXPRTRUE;EXPRFALSE)




Lima - Peru

 

ValueCalc = if(TODAY()>=EOMONTH(VALUES('Fiscal Calendar Date'[Fiscal Calendar Date]),0),1,0)

When I give the above statement, I'm getting error saying multiple values supplied instead of single value

"A table of multiple values was supplied where a single value was expected."

parry2k
Super User
Super User

Do you have dedicated calendar table? If yes then we can add another colculated column in calendar table, for example called "Months from Today" using DAX and then it will be straight forward to calculate but would like to know if you have dedicated calendar table in your dataset. L:et me know and will help from there.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k Yes I have a dedicated Calendar table with Year, Month and Date  as columns.

 

Below is the result I got. First I calculated the rolling 12 months sum and next I display Zero for cureent month and forward. So all Completed months will have rolling 12 months result displayed

Result.JPG

Peter_R
Resolver II
Resolver II

I was wrestling with creating a rolling average column and finally gave up trying to do it in DAX. I did in in the query, creating a custom function called fnRollingAvg, which looks like this:

let
CalcRollingAvg = ( pFactTable as table, pFactTableKey as text, pDataColumn as text, pStartDate as date, pEndDate as date ) as number =>
let
#"GetFilteredRows" = Table.SelectRows( pFactTable, each [FactKey] = pFactTableKey and [Date] >= pStartDate and [Date] <= pEndDate ),
#"CalculateAverage" = List.Average( Table.Column( #"GetFilteredRows", pDataColumn ) )
in
#"CalculateAverage"
in
CalcRollingAvg

=============

This could be changed to calculate the rolling sum rather than average by changing the List function from List.Average to List.Sum.

 

In my shaping query, I add a new rolling average column that invokes the function for each row, providing the begin and end dates to use for the calculation as parameters. This allowed the function to be used for any time range (12 months, 3 months, etc). The call looks like this:

 

#"FactValue_12Mo" = Table.AddColumn( stgSampleData, "12 Month Rolling", each fnRollingAvg( stgSampleData, [FactKey], "FACTVALUE", Date.AddDays( Date.AddYears( [Date], -1), 1), [Date] ), type number )

 

.... where the date column in the fact table is called 'Date', and the column being summed over is called 'FactKey'.  As written, this would perform the call for all periods, including the current month. If you wanted to force the current month average to be zero, you could wrap an If stmt around the function call to set the new column to zero if [Date] is the current month and otherwise invoke the function.

Anonymous
Not applicable

@Peter_R :  I tried using M language per your suggestion. I was able to succesfully invoke the function for a single record and not the entire column. I got the error "Expression.Error. A cyclic reference was encountered during evaluation.  Note: I do not have a factkey, so did not use it.Month_Fiscal has the dates.Here is the sample datasetSample.JPG

I used the following function:

= let
CalcRollingSum = ( pFactTable as table, pDataColumn as text, pStartDate as date, pEndDate as date ) as number =>
let
#"GetFilteredRows" = Table.SelectRows( pFactTable, each[MONTH_FISCAL] >= pStartDate and [MONTH_FISCAL] <= pEndDate ),
#"CalculateSum" = List.Sum( Table.Column( #"GetFilteredRows", pDataColumn ) )
in
#"CalculateSum"
in
CalcRollingSum
in
#"CalculateAverage"
in
CalcRollingAvg

Tried Invoking the function (using Custom column)

Table.AddColumn(#"Added Custom2", "Custom", each Query1( Sheet1, [NETSALES],Date.AddDays( Date.AddYears( [MONTH_FISCAL], -1), 1), [MONTH_FISCAL] ))

Hi there ... wow, that was a while ago. My DAX skills have improved significantly since then, and I think I'd be inclined to do this in DAX rather than in M now ... but having said that, I think there are few issues with your M code. I assume that you're calculating a rolling sum, not average? Your function appears to have an extra let in it - looks to me like your function should look like this:
let
CalcRollingSum = ( pFactTable as table, pDataColumn as text, pStartDate as date, pEndDate as date ) as number =>
let
GetFilteredRows = Table.SelectRows( pFactTable, each [MONTH_FISCAL] >= pStartDate and [MONTH_FISCAL] <= pEndDate ),
CalculateSum = List.Sum( Table.Column( GetFilteredRows, pDataColumn ) )
in
CalculateSum
in
CalcRollingSum

.... as for invoking it, I think this would work:
Table.AddColumn(#"Added Custom2", "12MoRollingSum", each Query1( #"Added Custom2", "NETSALES", Date.AddDays( Date.AddYears( [MONTH_FISCAL], -1), 1), [MONTH_FISCAL] ), type number)

... I'd suggest that you use more meaningful names than the generic ones created by the system (ie, things like 'Query1' and 'Added Custom2'. Apart from that though, the main thing is that your data column being passed to the  function is the name reference, not the actual column - ie "NETSALES" vs [NETSALES]. Also, the table being passed to the function needs to be the same table as what you're adding the new column to - so #"Added Custom2", not Sheet1.

Anonymous
Not applicable

@Peter_R : Thank you. This worked for the sample dataset. For the actual dataset, when I invoke the function, it keeps running. Not sure what has broken though. I was wondering , because I used the column [MONTH_FISCAL] as a reference, the query is going to validate each and every date there and take a long time to provide TTM numbers.

As mentioned earlier, this is actually better suited for DAX, assuming that you've got a full data model in place (eg, a date table). Power Query (ie 'M') has performance issues - for example, if your dataset has a lot of columns in it, then it will run much slower than one that has just a few columns). So you might treat this as an exercise in getting familiar with what is possible in M, even if it's not the best approach.

 

In DAX, you might try something like this - 
using the recommended idea of having building blocks of measures, create a measure for total net sales:
TotalNetSales = SUM( [NETSALES] )

Then a measure for rolling 12month sales:
12MonthRollingSales =
CALCULATE([TotalNetSales],
DATESINPERIOD(DimDates[Date],
LASTDATE(DimDates[Date]),-12, Month
)
)

... that's of course assuming that the fact table is connected to a proper date table (DimDates in my example, where [Date] is every calendar date - provided that MONTH_FISCAL is a proper date, then it can be linked to the date table)

ImkeF
Super User
Super User

Pls paste a picture of your desired result.

You might find this article helpful as well: https://social.technet.microsoft.com/wiki/contents/articles/28212.how-to-ask-a-power-pivot-question-...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF; Desired Result was pasted in my first post

 

Sorry for the late reply everyone.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.