Rolling 12 Financial Periods

[ells]

What I thought was quite simple turns out not to be. I need to calculate Rolling 12 Financial Periods and display it in a chart by Fiscal Periods.

If it was months there is kioads of info out there.

I can get to
Calculate( [Sales],
DATESBETWEEN(
dim Calendar[date].
**,
[EndDate]
)

I have the end date in a Measure. With Months I would use SAMEPERIODLASTYEAR. However this wont work as it is financial periods and they dont align like months.

There must be an easy way to step back 12 Financial Periods (not months)
The date dimension has a date column and a Financial Year Month number

I am sure there must be a real easy way to do this. I feel like I am missing the obvious

Thanks
E

[Greg]

Hi @ells. Grab @Melissa’s extended date table and see the use of offsets. If it doesn’t have exactly what you need for your financial periods, you should be able to apply the offset principle to your situation without too much effort. Greg

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

[Melissa]

Hi @ells,

The Financial Period is just another Attribute of Dates
See if this is helpful.

Sales 12M = 
VAR myStart =  MAX ( Dates[Date] )
VAR myEnd = EDATE ( myStart + 1, -12 )
VAR Result =
    CALCULATE(
        [Total Sales],
        FILTER( 
            ALL( Dates ),
            Dates[Date] >= myStart 
            && Dates[Date] <= myEnd 
        )
    )
RETURN

Result

[ells]

Thanks,
I thought EDATE would do calendar months? This really needs to be financial and I have to use the data dimension provided

So Easy in SQL
End date I have
Start date would be
a = get the financial Year Month Number for the end date
b = a-100 (so if its 202010 then it becomes 201910
c= add on to the Financial Year Month (watching year boundaries) so we get 201911
start date = first day of 201911

I am sure there must be an easier way. Juggling three issues at once is not good for my small brain

Many Thanks
E

[Melissa]

Can you post a copy/sample of your Dates table?

[ells]

Unfortunatelly I am not allowed to post it. There are a lot of things I am not happy about one of which is the naming convention. When I say Financial Months they are really Periods so do not follow the calendar Year.

I have the following columns of Interest
Month Year Number (financial Month Year Number)
Month Number (financial Month Number)
Month Offset ( financial offset from Cuurrent Month)

Hope this helps.
E

[Melissa]

I’m sure you can disclose what type of financial calendar you are using?
13 Period, 445, 454, 544 ???

[ells]

I am not sure. It looks like it is linked to start or end on a particular day. Very difficult to tell .
Best Guess would be Period end on last friday of the month.

Sorry I know this is going to be as awkward as possible.
E

[Melissa]

Ok then last question can you give me one start date for a new period.
You know what I’ll make something up

[ells]

So my Current End date is 27 July 2020
30 May 2020 would be the start of a period

Could you not use the Month Offset?
Thanks
E

[ells]

I was thinking along the lines of
VAr EndDate = [End date]
Var EndDateCurrentYearOffset= (need to get the CurrentYearOffset for EndDate)
Var StartDateCurrentYearOffset= VarEndDateCurrentYearOffset -11
Var StartDate = Min(dim Caleddar[Date] where Year Month Offset = StartDateCurrentYearOffset

From there I can calculate Sales with Dates Between start date and end date
?
Many Thanks
E

[Melissa]

Without seeing your Dates table I’m hoping the Month Offset is a non-repeating sequential number…
Can you give this a go

Sales 12P = 
VAR myOffset = SELECTEDVALUE( Dates[MonthOffset] ) 
VAR ListMonths = CALCULATETABLE( VALUES( Dates[MonthOffset]), FILTER( ALL( Dates ), Dates[MonthOffset] >= myOffset -11 && Dates[MonthOffset] <= myOffset ))
RETURN

    CALCULATE( [Total Sales],
        FILTER( ALL( Dates ),
            Dates[MonthOffset] IN ListMonths
        )
    )

I hope this is helpful

[JarrettM]

@ells,

Here is one I use in all my financial models. This example is TTM for Cost of Sales:

Total Cost of Sales TTM =
CALCULATE (
    [Total Cost of Sales],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
) 

I also have a date slicer at the top of my slide. Forgot to mention that.

Thanks
Jarrett

[ells]

@JarrettM
Many Thanks but its the financial periods that are causing the issue. I think what you have assumes the 12 Periods span 12 Months?
Cheers
E

[ells]

That looks like its worked
Many Thanks
E

[EnterpriseDNA]

It’s great to know that you are making progress with your query @ells. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[NeilR]

Hi there

I am trying to create a current R12 and prior R12.

I have one table with dates 01.07.2019 to 30.06.2021 which covers the periods.

I have the reports I want in excel and trying to recreate them in Power BI - this is useful as I can sense check them.

This formula is not giving me the correct answer for the current R12 period.

Sales 12P =
VAR myOffset = SELECTEDVALUE( Dates[MonthOffset] )
VAR ListMonths = CALCULATETABLE( VALUES( Dates[MonthOffset]), FILTER( ALL( Dates ), Dates[MonthOffset] >= myOffset -11 && Dates[MonthOffset] <= myOffset ))
RETURN

CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[MonthOffset] IN ListMonths
    )
)

It appears to be giving me a lower total than I would expect from my excel reports. How can this be resolved and then how would I use the offset to create the prior year R12.

I have copied in your massive date table to my model.

I am really finding the shift from Excel to POWER BI incredibly difficult but feel it is really worth the pain

[Keith]

Hi there,
Please open a new topic as this posting is already closed. People of the forum may not see your posting if you don’t open a new posting.

thanks
Keith

[NeilR]

How do i open a new topic please, Keith?

[Keith]

move mouse over the blue circle bottom right hand corner and it will turn into New topic and click on and then create it from there. Please remember to delete the other postings.

I hope this helps.
keith