Creating a Calculated table with multiple levels

[RobbeVL]

Hi all,

A little help with creating this table would be nice.
I have quite a complex dataset, but I would like to complete this "simple" DAX measure. 

I want a Calculated Table with the following columns:

Unit ('BU'[BusinessUnit])
Year ('Date'[Year])
SalesRate (Measure)
SalesRateLY (Same measure, but showing the previous year)

Please take into account that the Last 2 columns would be based on measures refering to multiple columns.
How do I build up this measure ? 

Robbe

[amitchandak]

The information you have provided is not clarifying the problem for me. Please explain with an example.

You can share sample and sample output data.

Appreciate your congratulations.

[RobbeVL]

I seem to get the correct result using the following:

SUMMARIZE(
              GENERATE ( 'Date'; 'Business Unit' );
               'Date'[Year];
               'Business Unit'[DIM_BUSINESSUNIT_ID];
                "Sales"; [SalesRate];
                "SalesLY; ???)

But how do I show the Sales of last year in this summarize?

Robbe

[v-frfei-msft]

Hi @RobbeVL ,

As we know we cannot create dynamic calculated column like that. Could you please tell me what is your formula of your measure [SalesRate]? Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.

[RobbeVL]

Hi v-frfei-msft , 

I was afraid this wouldnt be possible that way.

In the table I would need for example this measure:

Rebitda = CALCULATE([Balance]; 'R-Account'[PL6]= "REBITDA")

      Balance = SUM('ZZ_PCABOOKING_CORE'[DEBIT])+SUM('ZZ_PCABOOKING_CORE'[CREDIT])

Rebitda_LastMonth = Is a measure refering to the previous month.

Expected result being: (could also just be a date, but aggregated on month level))

Year	Month	BU	REBITA	REBITA_LM
2019	01	VW	100
2019	02	VW	120	100
2019	01	BV	100
2019	02	BV	120	100
...

thx,

Robbe

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output.

Appreciate your Kudos.

[RobbeVL]

Hi @amitchandak ,

I dont really know how to explain anymore. This is the way someone forwarded it to me. Seemed clear to me.
The result should be something like this:

BU is a Dimension, Year is from a dimension, but Sales is a measure based on 5+ tables with references to multiple other measures)
If I would drag and drop this in a matrix table, i'd get the result quite easy.

But how to "hardcode" this in a Calculated table? 

BU	Year	Sales	SalesLY
Unit 1	2019	1000
Unit 1	2020	2000	1000
Unit 2	2019	1500
Unit 2	2020	2000	1500

Regards,

Robbe