Forum Discussion
Filter on the measure.
The dates of tables I have are two, one that tells the year and one that informs the month both are relacionan through the fact table, just need to now see a way to use this as VOES passed me without the MAX function in time determine the year to calculate
Community ChampionAnonymous These Measure give Year and Previous Year and Year on Year Change and Year On Year %
Add the Year Slicer and then select at least 2 consective years!
Max Year = CALCULATE([Total Net], FILTER(Payments, Payments[PMT Year]=MAX(Payments[PMT Year])))
Max Year -1 = CALCULATE([Total Net], FILTER(Payments, Payments[PMT Year]=MAX(Payments[PMT Year])-1))
YoY Change = [Max Year] - [Max Year -1]
YoY % Change = DiVIDE ( [YoY Change], [Max Year -1], 0)
Let me know if you do decide to add a Caledar Table - there's a better way to do this.
- Sean
Anonymous
2016 - ( 2016 - 1) => 1
FILTER (DMEGF_Ano; DMEGF_Ano [Year] = MAX (DMEGF_Ano [Year]) - (MAX (DMEGF_Ano [Year]) - 1)))
delete the underlined part
- Sean
are you using DMEFG_Ano[Year] for the Slicer?
I tried the following expression and the value is blank:
Consumption Warehouse 2 = calculate (sum (Facts [Vl_ConsumoAlmox]) - sum (Facts [Vl_EstornoAlmox]), FILTER (DMEGF_Ano; DMEGF_Ano [Year] = MAX (DMEGF_Ano [Year]) - MAX (DMEGF_Ano [Year]) - 1))- Sean
Anonymous what are you trying to calculate? for what year?
this is what your FILTER is doing => 2016 - 2016 - 1
thats why you are getting BLANK
- Greg_Deckler
Consumption Warehouse 2 = calculate (sum (Facts [Vl_ConsumoAlmox]) - sum (Facts [Vl_EstornoAlmox]), FILTER (DMEGF_Ano; DMEGF_Ano [Year] = (MAX (DMEGF_Ano [Year]) - 1)))
correct the error and the information is being displayed however when I do a filter annually whether in 2016, 2015 or 2014 values are blank
yes, to correct that what you recommend? I create a dimenssão calendar?
- Sean
Do you have any other filters/slicers ON at the same time?
EDIT:
If you want to create a Date Table look here (Link by Matt)
http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
Yes, my reports have several filters
- Sean
Anonymous I tried these formulas with several Slicers and it works.
