How to right click table to show values?

0
votes

In a pivot table that plots values against a timeline it is possible to right-click the table, select "Show values as..." and have them appear as a percentage of a particular day.

I'm trying to recreate the same behaviour using DAX measures: I would like to have a measure that shows each day's price as a percentage of the first day of the year.

I've successfully created a measure that correctly identifies the first date of the year, i.e. the baseline: FDate:=CALCULATE(FIRSTDATE(Prices[Date]),ALLEXCEPT('Calendar','Calendar'[Year]))

However, I can't figure out how to use this FDate to get that day's price (needed as the baseline for further calculations):

  1. CALCULATE([Sum of Price], ALLEXCEPT('Calendar','Calendar'[Year]), FILTER('Prices', 'Prices'[Date]=[FDate])) returns each day's price, not the first date's.

  2. CALCULATE([Sum of Price], FILTER(ALLEXCEPT('Calendar','Calendar'[Year]),'Calendar'[Date]=[FDate])) ignores the YEAR report filter and returns the price of the very first date in my calendar table and not the first date in the year I've filtered for.

Any pointer in the right direction would be greatly appreciated! Thanks

1
powerpivotdax
@Rob, not sure why you edited my title. My question is definitely not how to right click to "show values as..."... – Tony
You said you wanted to right-click the table, select show values, and have them appear as a percentage. I should have added "as a percentage" if that helps but your original title was non-descriptive and contained an unnecessary tag. If you can write a better title, I can change it for you if you can't. – Rob

1 Answers

2
votes

Here's the solution:

VAR FirstDate = [FDate]

RETURN(

CALCULATE([Price], FILTER(ALLEXCEPT('Calendar','Calendar'[Year]),'Calendar'[Date]=FirstDate))

)

Variables allow you to define measure in a certain filter context but to leave it unaffected by subsequent filter contexts - that at least is my layman's understanding. More info here: https://www.sqlbi.com/articles/variables-in-dax/