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:


    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

  • 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