Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Rolling 12 Month Turnover Trend chart

  • 1.  Rolling 12 Month Turnover Trend chart

    Silver Contributor
    Posted 15 days ago
      |   view attached
    Dear All,

    I have developed Headcount and attrition report based on Monthly Transaction record.Find the attached sample report.
    Now one of the requirement is to show the Turnover data in Line chart as 12 Month rolling as Trend.
    For example If i choose  Year 2018 and Month December I have to Plot  from 31-DEC-2017 to 31-dec-2018.
    I am having single table (all values are populated as per employee transaction for the month) and Date table joined with Employee table.
    Hope I have to create new measure and arrive  dateadd(MONTH,-12,'31-dec-2018') like this.
    In DAX how to achieve this.Because I have to show the trend based on Department & Region based too.

    For Turnover I have created measure like below :

    TurnOverMonthly =
    VAR OpenBalance =
    CALCULATE ( SUM ( 'Employee'[OpeningBalance] ),FILTERS('Employee'[Period]) )
    VAR CloseBalance =
    CALCULATE ( SUM ('Employee'[ClosingBalance] ),FILTERS('Employee'[Period]) )
    VAR Leavers =
    CALCULATE ( SUM ( 'Employee'[leaverscnt] ),FILTERS('Employee'[Period]) )
    VAR result = ((Leavers
    / ( ( OpenBalance + CloseBalance )
    / 2.0 )))
    RETURN
    IF ( ISBLANK ( result ), 0.00, result )


    ------------------------------
    Sivaramakrishnan Gopalan

    ------------------------------

    Attachment(s)

    pbix
    Rolling12Months.pbix   118K 1 version
    Power Summit (bi) - Post


  • 2.  RE: Rolling 12 Month Turnover Trend chart

    Posted 14 days ago

    Perhaps look at using dax function DATESINPERIOD() and use:
    - start date as something like NOW()
    - no. of intervals as -12
    - interval as "month"

    ------------------------------
    Shane Glasheen
    Data Scientist
    +6163698141
    ------------------------------

    Power Summit (bi) - Post


  • 3.  RE: Rolling 12 Month Turnover Trend chart

    Top Contributor
    Posted 14 days ago
    Hi @Sivaramakrishnan Gopalan,

    I have no read the whole post, but from what I read, in order to do the trend analysis you have to filter your data table to show the past 12 months from the current date.

    So you can use whatever measure you created, in another CALCUALTE() function, then in the filter section, you can use the DATESBETWEEN() function and inside that you can mention the start date as DATEADD(TODAY(), -12, Months) and the end date as TODAY().
    This way when you put this new measure on the line graph, where the X-axis is your timeline, you can see the trend of the past 12 months.

    As far as region and department filters are concerned, you can put them on a slicer from your fact or dimension table (dimension table recommended) and you should be able to filter this new measure.
    If you want to create multiple line graphs​​, you can make one visual and then put a visual level filter on each of them for the specific department or region.

    Hope this helps solve your problem.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Power Summit (bi) - Post


  • 4.  RE: Rolling 12 Month Turnover Trend chart

    Silver Contributor
    Posted 12 days ago
      |   view attached
    Thanks Vishesh. PFA.I have written New measure rolling turnover and updated the graph.But in the report If i choose Year filter its plotting the values for all the months.But Year month selected its only filtering to particular month.How both filters applied i need to roll and show from selected period-6 or -12.Is it possible to cumulative the values ?


    ------------------------------
    Sivaramakrishnan Gopalan
    Chennai
    9444430950
    ------------------------------

    Attachment(s)

    pbix
    Rolling12Months.pbix   120K 1 version
    Power Summit (bi) - Post


  • 5.  RE: Rolling 12 Month Turnover Trend chart

    Silver Contributor
    Posted 8 days ago
      |   view attached
    Dear All,

    Turnover (last n months)  measure I have created using DatesInPeriod. But turnover values are not showing its showing zero.I have missed anything kindly guide me to close this.


    Regards
    Siva

    ------------------------------
    Sivaramakrishnan Gopalan

    ------------------------------

    Attachment(s)

    pbix
    RollingTurnover.pbix   114K 1 version
    Power Summit (bi) - Post


  • 6.  RE: Rolling 12 Month Turnover Trend chart

    Top Contributor
    Posted 7 days ago
    Edited by Vishesh Jain 7 days ago
    Hi @Sivaramakrishnan Gopalan,

    I saw your file and a couple of pointers:

    -You need a calendar table in your model and all your calculations/measures should be based on that calendar table.
    Functions like DATEADD() will not work if you don't have a calendar table. Make sure you check 'mark as data table' and select your date column in the new calendar table​, else PBI will still use its underlying calendar table.

    -I might be wrong in this one, but please check this part of your calculation, as averaging the opening balance and closing balance will inflate the denominator, so you might want to take either of them, preferable the closing balance. However you will know that better as how do you want to calculate it.
    VAR result = ((Leavers
    / ( ( OpenBalance + CloseBalance )
    / 2.0 )))

    Please can you check your calculations for Oct 2018, Nov 2018 and Dec 2018, if they are correct or not. If not, then please upload the sample file or an excel file with the right numbers and how you want the output to look like.

    As far as the other months of 2018 and the zeros are concerned, your table does not have any data for that, so it is likely to show 0.
    Please correct me if I am wrong and if I am missing something here.

    Lastly, all your time intelligence filters should flow through your calendar table and I can't emphasize enough for you to have one in each of your data models.

    My apologies for the long reply, but these are some things that I found lacking in your file, that could solve your issue.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Power Summit (bi) - Post


  • 7.  RE: Rolling 12 Month Turnover Trend chart

    Silver Contributor
    Posted 7 days ago
    Dear Vishesh,
    Thanks for your reply.Herewith I have attached my Excel working file for your reference.I want to show 12 month back rolling from the selected month and Year as line graph as trend this is my requirement.I have workout the calculation in Excel and PBI both are matching.Mark as Date table not done in the attached file.

    Requirement : If i choose Dec-2018 I have to show only from Dec-2017 to DEC-2018 in "X" axis and in values I have to show that month Turnover monthly%.
    I have uploaded both Excel and working latest PBIX file.Go through and share your thoughts.But the PBX file without Year month filter the values coming correctly but if i choose particular date its only showing values for the particular month and 12 month filtering is not happening in axis.

    ------------------------------
    Sivaramakrishnan Gopalan
    Chennai
    9444430950
    ------------------------------

    Attachment(s)

    xlsx
    Employee.xlsx   17K 1 version
    pbix
    RollingTurnover.pbix   113K 1 version
    Power Summit (bi) - Post


  • 8.  RE: Rolling 12 Month Turnover Trend chart

    Top Contributor
    Posted 7 days ago
    Edited by Vishesh Jain 7 days ago
    Hi @Sivaramakrishnan Gopalan,

    I will go through your file but meanwhile, in your rolling turnover, instead of using the LASTDATE() function, use the MAX() function.

    LASTDATE() will always take the last date of that month into consideration, whi​le MAX() will take the last date in the current filter context.

    This should take you in the right direction.

    Also, I doubt that your requirement will be possible, as dynamic filtering of the axis is not possible.
    However, there might be a workaround and I'll try to dig in and get back to you.
    It might not be exactly what you are looking for, but might just work.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Power Summit (bi) - Post


  • 9.  RE: Rolling 12 Month Turnover Trend chart

    Posted 14 days ago
    ​Hi,

    Have you tried using the YTD function using the 31/12/2018 as the year end?

    Example:

    TOTALYTD(SUM('Sales Order Line'[Quantity Kgs]),'Date'[Date],"9/30")

    Regards
    Mark

    ------------------------------
    Mark Clifford
    BI Analyst
    Thornton-Cleveleys
    07480272546
    ------------------------------

    Power Summit (bi) - Post