Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Dynamic Sum of X last columns in PowerBI

    Posted Jan 21, 2022 05:53 AM

    I have a set of excel files with financial data of public companies. Each file represent diffrent data - revenues, earnings, margings etc. Files have such a structure:

    Table

    Column A contains companies names and other columns have quarterly data. In my reports i show usually actual last quarter and YTD data (every quarter year to date). By now i am using Measures to calculate the sum of columns which contains data for a specific period but this solution isn't "smart". I have to manually change it every time when new data is added and I need to make another measures when i want to check some period from the past. And it goes like this for every table in the model. I am trying to find a way to use slicer as a dynamic way to choose number of last columns to sum up directly from the report but i can't find a solution. Is it even possible? Or maybe you have another idea how to make it more usefull than making manually a lot of measures for every table?



    ------------------------------
    Marek Jurek
    Analyst
    ------------------------------


  • 2.  RE: Dynamic Sum of X last columns in PowerBI

    Posted Jan 26, 2022 04:30 PM

    Hi Marek,

    I have a lots of documents with dates in columns and to be honest, I dont like it. All you need to calculate whatever you'll imagine is:

    Company name, date, Category, Value.

    Number of quater you cant calculate from Date using PBI TimeIntelligence.

    So I wil be in your shoes 1st I will be unpivot this table.

    Rest is big mistery as your questions are not enough detailed and without sample of data and result you want acheived is hard to answer.



    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 3.  RE: Dynamic Sum of X last columns in PowerBI

    Bronze Contributor
    Posted Jan 26, 2022 05:01 PM
    You are going to want to do this in powerbi --->> very easy. I could even help you do this for like $20

    You are going to want to convert your table to this format and conect the date column to a date table and use as CurrentMonth column

    Company Date Value
    X 2021 Q1 10
    X 2021 Q2 1
    X 2021 Q3 2
    X 2021 Q4 62
    Y 2021 Q1 16
    Y 2021 Q2 2
    Y 2021 Q3 61
    Y 2021 Q4 1
    Z 2021 Q1 16
    Z 2021 Q2 6
    Z 2021 Q3 1
    Z 2021 Q4 614


    ------------------------------
    Beau A
    Analyst
    MI
    ------------------------------