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

Yes I have a dates table

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")
    )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

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

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Am I inputting it incorrectly?