Formula for 12 months rolling trend

[Anonymous]

Hi,

I have problem creating formula for 12 months rolling trend in dax. Anyone can help me with the correct formula?

Thanks.

[v-xiaotang]

Hi @Anonymous 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
really appreciate!
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,
Community Support Team _Tang

[v-xiaotang · Accepted solution]

Hi @Anonymous 

you can try this

create a new column, and change its type to Date

Datecolumn = 'Table'[Year]&"/"&'Table'[Month]&"/1"

then create a measure

12MTHRolling =
VAR _range =
    DATESINPERIOD ( 'Table'[Datecolumn], MIN ( 'Table'[Datecolumn] ), -12, MONTH )
VAR _f =
    FIRSTDATE ( _range )
VAR _l =
    LASTDATE ( _range )
RETURN
    IF (
        DATEDIFF ( _f, _l, MONTH ) > 10,
        DIVIDE (
            CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ), _range ),
            CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ), _range )
        ),
        BLANK ()
    )

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[SharonB]

Hi Group!  I'm a new BI user and I've been stuck (for months--sigh) trying to set up a 12-month rolling count for late notices in my Late Sales table.  I've tried so many things and I'm just not able to make it work. The metric I need should determine whether a late notice was issued three times in a rolling 12-month period.  A Notice is issued when a sales report is not submitted on time for that month.  In the picture below, I have a column called Notice Issued that shows in which months a notice was issued.  The other column, R12M Total should be adding the months like 1, 2, 3, etc. but the formula returns a "1" in each case.  I do have a date calendar that is related to the Due Date in the Late Sales table.  Here is the latest formula that I've tried.  If anyone would be so kind as to try to help solve this I would appreciate it so very much.

R12M Total =

  CALCULATE(

  'Late Sales'[Count of Late Notice Month running total],

  DATESBETWEEN( 'Date'[Date],

  MAX('Date'[Date] )-365,

  MAX( 'Date'[Date] ) ) )

[v-xiaotang]

Hi @SharonB 

thanks for the @mention, I'll reply in your new post.

Best Regards,
Community Support Team _Tang

[TomMartens]

Hey @Anonymous ,

this article provides almost everything you need to know to solve date related requirements using DAX: https://www.daxpatterns.com/time-patterns/

If you need more guidance, please provide more details, especially about your data model and the structure of the tables. A dedicated Calendar table (as described in the above article) table is essntial.

Hopefully, this provides some more insights, and helps to tackle your challenge.

Regards,

Tom

[Anonymous]

Hi Tom, 

Thanks for reply. the first two column represent the year and month of my data. It means the time pattern required is Month-related calculations. 

Based on 2020 data, I would like to know the 12 month rolling trend for 2021 onwards but I could not derive the formula in dax. As the highlighted is the result I want, which is average 12 months sales.

[TomMartens]

Hey @Anonymous ,

Power BI, or being more precise the in-build data model, does not know anything about date or time, and for this reason, it does not know anything about a sequence, that can be derived from "funny" names like "Jan" or "Feb". For this reason it's not possible to create a DAX statement that allows to calculate what you are looking for.

You have to to provide a hint, the Calendar table.

First create a date column in your data table, then create calendar table, relate the calendar table with your data table. Finally, you can take one of the many examples from the article I mentioned in my first post, adapt the DAX to your needs.

Regards,

Tom