Forum Discussion
Rolling 12 Month Calculation Issue
I have been through many of the top answers to this type of issue and still can't seem to come to a solution.
When I attempt to calculate a Rolling 12 Month Sum of a single fields, I either get the total for a given month (i.e., Rolling 12 Month Items and Items column are the same) or an extremely high amount.
Below is an example of one of the measures I have tried to use (note: in this attempt I have hard coded the start and end dates to make sure my other measures weren't acting weird and causing problems with the date filter).
I would then create a simple table with the Process Month, Items sum, and this measure and the measure would just be a copy of the Items sum column
Rolling 1 Year Items =
VAR _Start = DATE(2018,12,31)
VAR _End = DATE(2019,12,31)
RETURN
SUMX(
CALCULATETABLE(
'ACH Originations',
DATESBETWEEN(
'ACH Originations'[Process Month],
_Start,
_End
)
),
'ACH Originations'[Items]
)
4 Replies
Anonymous not sure why you are doing calculate table etc, let's look at the fixed dates, I assumed you have date dimension in your table and linked to your data table, if not then read about it here
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/Rolling 1 Year Items = VAR _Start = DATE(2018,12,31) VAR _End = DATE(2019,12,31) RETURN CALCULATE ( SUM ( 'ACH Originations'[Amount] ), DATESBETWEEN( DateTable[Date], _Start, _End ) )Let's get it working and then we can get to the next level..
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
I've gone ahead and created a calendar table according to the method shown in the link you provided and related the Date field from the calendar to the ProcessMonth field from my data table.
I have also replicated the measure you have shown as well and it looks like it is still restricted to a single month.
Side note: I have a Process Month and a Process Date field in my data table (process month is simply the end of month of the process date)
Anonymous not sure why it is not working, it is hard to say without looking at the data, can you share pbix file (remove sensitive information before sharing) and let's go from there. You can share using one drive/google drive or send it directly to me via email, it is in the signature.
