Forum Discussion
INDIRECT function in DAX
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
3 Replies
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
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.
