Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  last 13 Month in visual

    Silver Contributor
    Posted 15 days ago
    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
    ------------------------------


  • 2.  RE: last 13 Month in visual

    Posted 13 days ago

    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
    Posted 7 days ago
    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
    Posted 4 days ago
    Hi @Adam Artur Boltryk

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


    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------