Change Dax Formula or Fields in Power Pivot

[Defumator]

Hello, everyone.

I have a base with data from different years, months, counts, real or budget, etc... And i am making a Profit and Loss Pivot table in Power Pivot.

On the row fieds, i have the different counts and sub counts.

In the column field, i have Calculate Fields with Dax. But i need to change them every month. For example, in feb, i have the calculated fields "Real Feb/17", "Real Feb/16", "Budget Feb/17", "Budget Feb/17", YTD Real Feb/17",YTD Budget Feb/17". In March, i have to change everyone one of them.

I am currently using excel 2013, what options do i have? I was thinking VBA, but i cant create measures in excel 2013 with VBA.


Thank you!

 

[akice]

If i understand what you want, you don't need (or want) to use VBA. That is what filters/slicers are for. You write just 6 measures and change the filter context to show the data you need.

 

[Defumator]

Yes, but i still ll have problems.

For example, if i put the months field in a slicer or filter by month. And i select "Mar", i wont have the YTD, just the month value. And if i select "jan", "fev" and "mar", i ll have the accumulated for the 3 months. I want to show the month value and the accumulated value.

 

[akice]

Do you have a separate Calendar table?

 

[Defumator]

I do not, but i know how to add it.

 

[akice]

Real Amount := SUM ( RealTable[Amount])

Budget Amount := SUM ( BudgetTable[Amount] ) or SUM( RealTable[BudgetAmount] )

Real Prior Year := CALCULATE ( [Real Amount], SAMEPERIODLASTYEAR(Calendar[Date] ) )

Budget Prior Year := CALCULATE ( [Budget Amount], SAMEPERIODLASTYEAR(Calendar[Date] ) )

YTD Real := TOTALYTD ( [Real Amount], Calendar[Date] )

YTD Budget := TOTALYTD ( [Budget Amount], Calendar[Date] )

So then just need to add some slicers to select the period you want.

 

[Defumator]

It worked! Thanks a lot!! I didn't know TOTALYTD and SAMEPERIODLASTYEAR