INDIRECT function in DAX

[Anonymous]

Hi all,

I have a problem. I want to create a dynamic measure in DAX that will depend of the name of the variable.

I have a table with column names display like this Month 1, Month 2,..... Month n, now depending of the current month or the selected month , the formula will display the value of the month selected and the one of the previous month. I want a formula like this SUM(Month n) + SUM (Month n-1). This is possible in Excel using INDIRECT function, what about DAX!!!!

Thank you

[Anonymous]

Hi. did you find a DAX function similar to "indirect" in Excel? I've been search the net but no success yet. Would appreciate your feedback.

[Ashish_Mathur]

Hi,

Assuming you have a Calendar table with Year and Month columns in there, build a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  Create 2 slicers - one for Year and another for Month - select any one Year and Month.  Write these measures

Total = SUM(Data[Amount])

Total for this and previous month = CALCULATE([Total],DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-1),MAX(Calendar[Date])))

Hope this helps.

[MFelix]

Hi @Anonymous ,

I'm assuming that your data is on the format like below:

KPI	1	2	3	4	5	6
Value	1	1	1	1	1	1

If this is the case believe that the best option is to unpivot the table and have something like this:

KPI	Month	Value
Value	1	1
Value	2	2
Value	3	3
Value	4	4
Value	5	5
Value	6	6

Then you can create a measure similar to this:

Total 2 month =
CALCULATE ( SUM ( Table1[Value] ) )
    + CALCULATE (
        SUM ( Table1[Value] );
        FILTER ( ALL ( Table1[Month] ); Table1[Month] = ( MAX ( Table1[Month] ) - 1 ) )
    )

If you don't want to unpivot your columns then you need to do it using several measures and a switch function.

Can you share a sample of you data and expected result.

thank you.

Regards,

MFelix