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

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

/ J

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.