Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 show...
Data Protection
Show and Tell
Tips and Tricks
Tutorial Requests
  • tex628's avatar
    tex628
    5 years ago

    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! 

tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
5 years ago

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
 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.