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

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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