topic how to Sum between two columns dynamically in DAX Commands and Tips

how to Sum between two columns dynamically

UrAvgWally — Mon, 15 Aug 2022 15:32:16 GMT

Hi all,

I was trying to figure out a measure which will be able to sum between two columns which are variable.

For example I have a report which columns are Period 1, Period 2, Period 3, Period 4.....Period 13.

I want to be able to sum between 2 variable columns.

For example if my current period is say Period 5,

I want to be able to add columns between Period 3 to Period 5.

And next period if I am in Period 6,

I would want to add between period 3 to Period 6.

I would like to change the benchmark period (Period 3 in the example) and current period dynamically.

Is it possible?

Like if user input somehow what the current period is and benchmark period.

Any help would be appreciated greatly 🙂

Re: how to Sum between two columns dynamically

tamerj1 — Mon, 15 Aug 2022 15:59:28 GMT

Hi UrAvgWally
Yes possible but depends on the your model, existing relationships, current filter context. I can only guess that you have a matrix visual were you have the periods on the row or on the columns and a measure (or summarized column) in the values. I don't know if they belong to the same table? If you have a Date table? Any relationships? Any of my assumptions is correct?

Re: how to Sum between two columns dynamically

UrAvgWally — Mon, 15 Aug 2022 16:16:12 GMT

Hiya! The above column's are in a raw data file, no filters or existing relationships. I will try explaining my dataset.

So I want to create a table which one column is the Funder and the other would be the sum , sum being COWD project to date + Period 3 + Period 4 + Period 5. I would like to have an Option to determine what the current period is , and what the base line period is and sum everything in between. So hypothetically if I am in Period 5 atm, I want to be able to sum Period 4 and Period 5. .. If I am in Period 8 and I chose my baseline period to be Period 6, I want to be able to add Period 7 and Period 8 and Cowd Project to date.

So it would be nbice to be able to chose my current period and baseline period and add dynamically like that.

Re: how to Sum between two columns dynamically

tamerj1 — Mon, 15 Aug 2022 16:59:26 GMT

Hi UrAvgWally
In order to do that the periods have to attributes in a one column [Period]. In order to do that you need to unpivot all the period columns using power query. It is a one click operation >> select the columns that need to be unpivoted, right click and select "Unpivot columns". But PLEASE BECAREFUL if you have large data, your file might crash. So please before doing anything PLEASE KEEP A BACKUP copy of your file. Once you do that, please let me know in order to guide you through the next steps.

Re: how to Sum between two columns dynamically

UrAvgWally — Tue, 16 Aug 2022 09:09:48 GMT

Hiya tamerj1 !
Thank you for your time!
I unpivoted those periods, but it makes columns like COWD repeat itself. Which will make my other calculations harder. For example I planned on doing sum(Cowd)-sum(Forecast) to get variances. But since this duplicates the figures, would not be able to do so easily. Is there a way to unpivot to a seperate query just that extract?Also after unpivoting , what steps should be used to get my desired output of adding between two variable periods :)?