How can I use a value in a column as part of a formula?

[Pitrus]

Hi,

I would like to make this formula more generic and taking the value "W29" (or other weeks..) from a column that stores this info. W29 is indeed the table name and is present in column [Reporting Week] as well. Of course, we have many more tables for w28, w27 and so on

Original formula that works but is not mantainable:

SOH = IF([Reporting Week]="W29",CALCULATE(SUM(W29[Entr/bes.]),ALL(W29),W29[Dem/need]="Stock",W29[Article]=earlier('Material/week'[Material]), W29[Date2] <= EARLIER('Material/week'[date])) ...

What I try to do (in excelish terms, using INDIRECT)

SOH = CALCULATE(SUM(INDIRECT(earlier[Reporting Week]&[Entr/bes.]),ALL(INDIRECT(earlier[Reporting Week]),INDIRECT(earlier[Reporting Week]&[Dem/need]="Stock",INDIRECT(earlier[Reporting Week]&[Article]=earlier('Material/week'[Material]), INDIRECT(earlier[Reporting Week]&[Date2] <= EARLIER('Material/week'[date])) 

Is there any option to do this in DAX? 

Thanks in advance community!

[amitchandak]

@Pitrus , what are you trying to get here.

In case you are looking for  WOW data, refer

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

[Pitrus]

Hi,

Thanks for the answer.

I am actually looking for referring a table name as a variable. Something like this:

SOH2 = 
VAR W=[Reporting Week]
RETURN
CALCULATE(SUM(W[Entr/bes.]),ALL(W),W[Dem/need]="Stock",W[Article]=earlier('Material/week'[Material]))

But it does not work 😞

[Anonymous]

This is NOT the way Power BI works. You can't have many tables, one for each week, and make it work. You have to arrive at one table with all the weeks in there.

Please first try to learn the basics of data modeling in dimensional models. There are many good vids on YT about this. Just search for it.

Best
D