Forum Discussion

MJT03's avatar
MJT03
Frequent Visitor
3 years ago

Maintaining Week Over Week Historical Data

Hi there. Relatively new-ish to PBI & looking for assistance on how to automate week over week reporting inPowerBI. 

 

We currently have teams recording work units in various Jira ticket fields. They continuously update those fields as the work progresses. My aim is to report on these changes on a weekly cadence (eg., each Weds) despite the data source constantly changing. Jira is already connected to PBI. 

 

My dream world would be that each week on Weds., I refresh the Jira data in PBI and the numbers autofill and append into a column that corresponds to the week # of the year, and I can have a visualization that bumps the prior week unit counts column against the current week Jira unit counts column. 

 

I don't know how to automate a weekly static capture of data. Any insight would be great. Thank you! 

Need Help
Tutorial Requests

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    Hi, MJT03 

     

    you can leverage Power BI's built-in data refresh capability and Jira's rest API. 

     

    1. Connect Power BI to Jira using the Jira connector.
    2. Design your report in Power BI with the required visuals and columns.
    3. Set up a scheduled data refresh in Power BI to occur every Wednesday.
    4. Use Power Query Editor to transform and append Jira data, including calculating weekly unit counts.
    5. Build visualizations, such as a column chart comparing current and prior week unit counts.
    6. Publish the report and share it with stakeholders.

     

    • MJT03's avatar
      MJT03
      Frequent Visitor
      3 years ago

      This is so helpful and straighforward, thank you! Can you elaborate on Step 4 and provide insight on how to begin that work? That seems to be the missing link for me. Thank you again!

      • rubayatyasmin's avatar
        rubayatyasmin
        Community Champion
        3 years ago

        Happy to help. Kudos will be more appreciated. 

        Sure. Elaborating the steps. though it will be a long process trying to give you an idea. 

         

        1. In Power BI Desktop, open the Power Query Editor.
        2. Select the query that retrieves the data from Jira.
        3. Apply necessary transformations (e.g., remove columns, change data types, filter rows).
        4. Ensure you have a date column for work unit records.
        5. Create a new column for the week number using the Date.WeekOfYear function.
        6. Group the data by week number and calculate the sum of work units.
        7. Append the transformed data from each week.
        8. Close and apply the changes to load the data into your report.

         

        NOTE: 

        Check the tutorial for PQ. https://learn.microsoft.com/en-us/power-query/power-query-ui

         

        also you can look for youtube for basic tutorial for Power Query in Power BI.