Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
tex628
Community Champion
Community Champion

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
Anonymous
Not applicable

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 @Anonymous ,

 

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.
Anonymous
Not applicable

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 

Anonymous
Not applicable

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

Anonymous
Not applicable

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 

Anonymous
Not applicable

tex628
Community Champion
Community Champion

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

/ J


Connect on LinkedIn
Anonymous
Not applicable

Yes I have a dates table

tex628
Community Champion
Community Champion

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


Connect on LinkedIn
Anonymous
Not applicable

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")
)
)
tex628
Community Champion
Community Champion

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
Anonymous
Not applicable

Maleehah_0-1628078455366.png

Am I inputting it incorrectly?

tex628
Community Champion
Community Champion

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
Anonymous
Not applicable

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

Maleehah_0-1628081417779.png

 

tex628
Community Champion
Community Champion

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
Anonymous
Not applicable

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

tex628
Community Champion
Community Champion

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
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.