Forum Discussion
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 shown for.
However, on the same table I need to total anything which comes after that 6 months.
So I have a date table and Pipleline table with delivery date and financed amount
Is there a way to do this?
The picture is too small for me to read! But the current rolling 6 months I'm thinking should be:
Aug 2021
Sep 2021Oct 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!
