cancel
Showing results for
Did you mean:
Helper V

## Rolling 6 months

Hi, I need to calculate a dax measure for a matrix table.

The goal is to have a table with pipeline data for the next 6 months finance by each division which is the category the finance will be shown for.

However, on the same table I need to total anything which comes after that 6 months.

So I have a date table and Pipleline table with delivery date and financed amount

Is there a way to do this?

1 ACCEPTED SOLUTION
Super User

The picture is too small for me to read! But the current rolling 6 months I'm thinking should be:

Aug 2021
Sep 2021

Oct 2021
Nov 2021
Dec 2021
Jan 2022

If you don't want the dates before the 1st of Aug (Current month) you can add the following to the statement:

``````Calculated Column =
VAR FutureMonth = IF(MONTH(TODAY()) > 6 , MONTH(TODAY())-6 , MONTH(TODAY()) + 6)
VAR FutureYear = IF(MONTH(TODAY()) > 6 , YEAR(TODAY())+1 , YEAR(TODAY()) )

Return

IF(Date Table[Date] < DATE(YEAR(TODAY()), MONTH(TODAY()) , 1) , BLANK() ,
IF(Date Table[Date] >= DATE(FutureYear, FutureMonth, 1) , "Rest of the Dates" , [month] )
)``````

Then put a visual level filter to remove the blank values from the visual!

20 REPLIES 20
Helper V

Also, Its actually not letting my sort the Rolling 6 months measure by the sort order I have. Maybe thats why I cant get it in order. Is there a way around this?

Community Support

Hi @Maleehah ,

If you are talking about sorting the matrix by specific column, I'm afraid it's not supported currently.

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Helper V

Also in relation to this would anyone know why my dates are not in chronological order? Its wrong in my dates table and in my matrix

Helper V

Also need the 6 months to be shown as Jan Feb March April etc

Helper V
 Division Out of date 6 months Rest of the dates Total 1 £1000 £5000 £7000 £1300 3 £1000 £5000 £7000 £1300 4 £1000 £5000 £7000 £1300

something like this but in power bi with the months above all columns

Helper V
Super User

Do you have a calendar table connected to your sales table?

/ J

Helper V

Yes I have a dates table

Super User

Does that dates table have a column for month? (Jan 2021, Feb 2021 etc)

Helper V

Yes it does I created a virtual dates table using this

Date Table = Var Basecalendar =
CALENDARAUTO(6)
Return
GENERATE(
BaseCalendar,
Var BaseDate = [Date]
Var YearDate = Year (BaseDate)
Var MonthNumber = Month (basedate)
Return Row (
"year", basedate,
"month number", monthnumber,
"month", format (basedate, "mmmm"),
"year month", Format (BaseDate, "mm yy")
)
)
Super User

``````VAR FutureMonth = IF(MONTH(TODAY()) > 6 , MONTH(TODAY())-6 , MONTH(TODAY()) + 6)
VAR FutureYear = IF(MONTH(TODAY()) > 6 , YEAR(TODAY())+1 , YEAR(TODAY())

Return

IF(Dates[Date] >= DATE(FutureYear, FutureMonth, 1) , "Rest of the Dates" , [month] ) ``````

Let me know how it looks.

/J

Helper V

Am I inputting it incorrectly?

Super User

Sorry, add another ")" after the FutureYear variable:

And your date table appear to be named "Date Table" so change that as well.

Calculated Column =
VAR FutureMonth = IF(MONTH(TODAY()) > 6 , MONTH(TODAY())-6 , MONTH(TODAY()) + 6)
VAR FutureYear = IF(MONTH(TODAY()) > 6 , YEAR(TODAY())+1 , YEAR(TODAY()) )

Return

IF(Date Table[Date] >= DATE(FutureYear, FutureMonth, 1) , "Rest of the Dates" , [month] )

Helper V

Hi, so i've added that but im getting more than 6 months and Jan for 2022

Super User

The picture is too small for me to read! But the current rolling 6 months I'm thinking should be:

Aug 2021
Sep 2021

Oct 2021
Nov 2021
Dec 2021
Jan 2022

If you don't want the dates before the 1st of Aug (Current month) you can add the following to the statement:

``````Calculated Column =
VAR FutureMonth = IF(MONTH(TODAY()) > 6 , MONTH(TODAY())-6 , MONTH(TODAY()) + 6)
VAR FutureYear = IF(MONTH(TODAY()) > 6 , YEAR(TODAY())+1 , YEAR(TODAY()) )

Return

IF(Date Table[Date] < DATE(YEAR(TODAY()), MONTH(TODAY()) , 1) , BLANK() ,
IF(Date Table[Date] >= DATE(FutureYear, FutureMonth, 1) , "Rest of the Dates" , [month] )
)``````

Then put a visual level filter to remove the blank values from the visual!

Helper V

Ahh perfect that seems to be more accurate.

If i wanted to name the column before August as its coming up with values but no column name can I insert that instead of BLANK?

This column would include everything from before the 6 months^

Also thank you for your help:)

Super User

Exactly, change the BLANK() to whatever you want the column to be named and it should hold the sales amount before the 6 months.

I would expect there to be a grand-total by default. Otherwise you should be able to enable it in the format options of the visual, under "subtotals".

Regarding the sorting order it's currently sorting in alfabetical order, which is the default order of text values. If you want to change this you will need to create a sorting column that is numeric, go into the column settings of your original column and change the "Sort by" setting to the new numeric sorting column.

I'd advice you to create a sorting column that is just the year and month, (2101, 2102, etc) and have other text values as 1 or 9999 depending on where you want them positioned.

Br,
J

Helper V

Hiya thats great thank you.
For some reason the table will not sort I've tried a few different ways but im not sure if its because the date table is a virtual one?

But other than that and the grand total it looks great!
I've had to turn off column sub totals as its creating totals within the year too which is what I dont want.

Helper V

Is there also a way to add a grandtotal to the end of all columns to summarise?

Helper V

Would you also know how to total everything thats after those 6 months?

so if the next 6 months ends in Jan anything after that for the next 2 years is totaled.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!