Forum Discussion

MalavikaUB's avatar
MalavikaUB
New Member
2 years ago
Solved

Rolling 13 periods without a structured date column

Hi, I would need help with rolling 13 period.  I have these columns from the table - FiscalYear and FiscalPeriod, Revenue. And not a date column. The requirement is to show the Revenue for the Rolli...
General Comment
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MalavikaUB ,

    Please try this way:
    Use this DAX to create a new measure:

    SelectedFlag = 
VAR Current_Year = SELECTEDVALUE(CurrentYear[CurrentYear])
VAR Current_Period = SELECTEDVALUE(CurrentPeriod[CurrentPeriod])
VAR CurrentPeriodNumber = (Current_Year * 100) + Current_Period
VAR MinPeriodNumber = CurrentPeriodNumber - 100
RETURN
IF(
    NOT ISBLANK(Current_Year) && NOT ISBLANK(Current_Period),
    IF(
        (SELECTEDVALUE('Table'[FiscalYear]) * 100 + SELECTEDVALUE('Table'[FiscalPeriod])) > MinPeriodNumber && 
        (SELECTEDVALUE('Table'[FiscalYear]) * 100 + SELECTEDVALUE('Table'[FiscalPeriod])) <= CurrentPeriodNumber,
        1,
        0
    ),
    BLANK()
)

    The results are shown below:

     

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

MalavikaUB , Create one table with FiscalYear Fiscal Period , say Period

 

Create new column

Year Period =  [FiscalYear]*100 + [Fiscal Period]

 

Create a Rank column

 

Period Rank = RANKX(Period,Period[year period],,ASC,Dense)

 

Then you can measure like

Last 13 Period = CALCULATE(sum('Table'[NetRevenue]), FILTER(ALL(Period),

Period[Period Rank]<=max(Period[Period Rank]) && Period[Period Rank]>=max(Period[Period Rank])-13))

Or you can use window function
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

  • MalavikaUB's avatar
    MalavikaUB
    New Member
    2 years ago

    Thanks for the response. 

    I did try the solution using RANKX function to fetch only the last 13 months of data in the visual. However, it does not really go well. Could you please assist me?