Hello,
As said by Adam Artur, it is a real best practice to use a calendar table, would say mandatory to use one :)
When calendar is connected to your Data table, then you measure will be :
13 month rolling =
VAR _MaxDate = EOMONTH( TODAY(), -1) --Retrieve the last day of previous month
VAR _DatesRange = DATESINPERIOD('Calendar'[Date], _MaxDate, - 13 , MONTH) -- Dates range to be used in the visual
RETURN
IF(
MAX('Calendar'[Date]) IN _DatesRange,
[Count of Requisition status]
)
I hope this is a good start for you
------------------------------
Nicolas MENDEZ
Data & BI consultant
Becom Consulting
------------------------------
Original Message:
Sent: Jun 18, 2022 05:30 PM
From: Adam Artur Boltryk
Subject: last 13 Month in visual
Hi Ganesh,
OFC - all is possible.
However, you need some preparation.
1) I assume, you have your calendar in DIM table. If not, it's mistake, you have to fix in first instance.
2) You have to create separate column with year and month, best as a number and I have always format YYYYMM -> 202201 202202 etc.It's number and working good, and most important - when you sort it it's always sort correctly.
3) Create column with RANK - eg. 'Month Rank' ->
RANKX('Calendar', 'Calendar'[YearMth], 'Calendar'[YearMth], asc, dense)
4) Create column RELATIVE
Var currentmonth = LOOKUPVALUE('Calendar'[Rank], 'Calendar'[Dates], TODAY())
Return
'Calendar'[Rank] - currentmonth
Now just set filters on your visual - relative is Lower than 0 and bigger than -13
Regards
------------------------------
Adam Artur Boltryk
Business Analyst
Original Message:
Sent: Jun 16, 2022 12:14 PM
From: Ganesh Kumar
Subject: last 13 Month in visual
Hi All,
i need help in DAX or any method to show last 13 month rolling in graph,
attached two picture here , one is showing Jan to Dec data this what current am getting in my visual ,
another one is may'21 to may'22 data in graph, this what am expecting in my graph,
is it possible to achieve second (may to may data) graph using only calendar date ? if yes please help me. and this should be dynamic if am doing refresh in next month ( July) then my graph show Jun'21 to Jun'22 data in graph
------------------------------
Ganesh Kumar
Analyst
------------------------------