cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maleehah
Helper V
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

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! 


Connect on LinkedIn

View solution in original post

20 REPLIES 20
Maleehah
Helper V
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?

Hi @Maleehah ,

 

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

 

Best Regrads,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Maleehah
Helper V
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 

Maleehah
Helper V
Helper V

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

Maleehah
Helper V
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 

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

/ J


Connect on LinkedIn

Yes I have a dates table

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


Connect on LinkedIn

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")
)
)

Add a new calculated column to your dates table:

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 


Connect on LinkedIn

Maleehah_0-1628078455366.png

Am I inputting it incorrectly?

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] )


Connect on LinkedIn

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

Maleehah_0-1628081417779.png

 

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! 


Connect on LinkedIn

View solution in original post

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:)

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
 


Connect on LinkedIn

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.

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

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.