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! 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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