last 13 Month in visual

last 13 Month in visual
Ganesh Kumar
Ganesh KumarJun 16, 2022 12:14 PM
Hi All, i need help in DAX or any method to show last 13 month rolling in graph, attached two picture …
1. last 13 Month in visual

Silver Contributor
Ganesh Kumar
Posted Jun 16, 2022 12:14 PM | view attached (2)
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
——————————
e7781e6d-b751-4621-88da-7d18f7a99691_file
×
1 / 2

2. RE: last 13 Month in visual

Adam Artur Boltryk
Posted Jun 18, 2022 05:31 PM
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
——————————

3. RE: last 13 Month in visual

Top Contributor
Nicolas MENDEZ
Posted Jun 24, 2022 05:37 PM
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
——————————

4. RE: last 13 Month in visual

Silver Contributor
Ganesh Kumar
Posted Jun 27, 2022 06:40 AM
Hi @Adam Artur Boltryk

thanks for suggestion and its worked, ​now i have 13 month rolling month in my graph,
​

——————————
Ganesh Kumar
Analyst
——————————