Dynamic Columns

[nmckbcs]

Hello! Is there a way in Power BI to ensure that as I add new items to my file that it dynamically adds the columns to my calculation? Right now my variance formula looks like this: Var_Planilla_Integral_2 = SUM('Planilla Integral (2)'[2016-17])-SUM('Planilla Integral (2)'[2017-18]) .... and as time progresses I will be looking at this over multiple categories and was hoping that somehow I could get Power BI to grab all of those fields? Or do I need to add them manually. The file I am referencing is below. 

https://drive.google.com/open?id=1bljr8wBK7nH8tOKDSNQQOf1xcdcJQBMJ - Power BI File

https://drive.google.com/open?id=10erRiMIuK_m-9kXkNq5L7H6_ggvqRAEB - Expected Results

Thanks!

[nmckbcs]

Hello! Is there a way in Power BI to ensure that as I add new items to my file that it dynamically adds the columns to my calculation? Right now my variance formula looks like this: Var_Planilla_Integral_2 = SUM('Planilla Integral (2)'[2016-17])-SUM('Planilla Integral (2)'[2017-18]) .... and as time progresses I will be looking at this over multiple categories and was hoping that somehow I could get Power BI to grab all of those fields? Or do I need to add them manually. The file I am referencing is below. 

https://drive.google.com/open?id=1bljr8wBK7nH8tOKDSNQQOf1xcdcJQBMJ - Power BI File

https://drive.google.com/open?id=10erRiMIuK_m-9kXkNq5L7H6_ggvqRAEB - Expected Results

Thanks!

[Ashish_Mathur]

Hi,

I'd like ot help but language is proving to be a barrier.  Can the column titles of your Planilla Integral dataset be in English please.  Also, i will need that source table to work with.  Share that dataset with column titles in English, if possible.  Also, i see that in the Query Editor, you have carried out some transformations.

It will be ideal if you can just share the dataset (input) and show me the result you are expecting (output).

[johnmu]

Is it possible to do something like create a PCTEMP sequence table with one column being the different temporadas and the other being the sequence, and then using LOOKUP with an offset for the sequence? So compare the sum of PCAA for the current temporada with the sum of the PCAA for the temporada sequence -1?

see the link .pbix file https://drive.google.com/open?id=1SgodCEaH9NwCJ3VjNEaybPJEi-1YsPzV

This might be a bit clunky, but I think you get the idea.  

There is a sequencing table, which is referenced by a calculated sequence column in your Planilla Integral

PCTEMP_Seq = LOOKUPVALUE(tblPCTempSeq[PCTEMP_Seq],tblPCTempSeq[PCTEMP],'Planilla Integral'[PCTEMP])

That's referenced to get the corresponding temporada for the previous period

PCTEMP_Prev = LOOKUPVALUE(tblPCTempSeq[PCTEMP],tblPCTempSeq[PCTEMP_Seq],'Planilla Integral'[PCTEMP_Seq]-1)

Then you can get the total PCAA (or whatever column you care about) for the current and previous period with

PCAA Current = 

CALCULATE(sum('Planilla Integral'[PCAA]),FILTER(ALL('Planilla Integral'),
        'Planilla Integral'[PCTEMP]=EARLIER('Planilla Integral'[PCTEMP])&&
        'Planilla Integral'[PCDL01]=EARLIER('Planilla Integral'[PCDL01])))

and

PCAA Prev = 

CALCULATE(sum('Planilla Integral'[PCAA]),FILTER(ALL('Planilla Integral'),
        'Planilla Integral'[PCTEMP]=EARLIER('Planilla Integral'[PCTEMP_Prev])&&
        'Planilla Integral'[PCDL01]=EARLIER('Planilla Integral'[PCDL01])))

Then one final measure to compare

PCAAVar = AVERAGE('Planilla Integral'[PCAA Current])-AVERAGE('Planilla Integral'[PCAA Prev])

This probably isn't as flexibile or efficient as you want, but I thnk it's the right direction to go in. I'll stay subscribed to see what if anything better anyone else can come up with.