I am using a table that shows YTD wholesale growth with the following DAX-query:
Wholesale Growth = ([Total Wholesale]-Calculate([Total Wholesale];SAMEPERIODLASTYEAR(Real_date[Date])))/Calculate([Total Wholesale];SAMEPERIODLASTYEAR(Real_date[Date]))
This return the following results:
The problem is that there is a year filter connected to the table. When selecting years 2015-2018 it works fine. But when selecting 2019 the results get messed up because for Q1 it returns for 2018 Jan/Feb/Mrt and for 2019 only Jan because that is all the data I have right now.
So what I want is that when selecting 2019 of the filter. Only 2018 january data is considered in the calculation, instead of all three months. This will empty the Q1(feb & mrt), Q2, Q3 and Q4 data, keeping only data in Q1(Jan).
So in short: When year selected is 2019 only show January data of both 2018 and 2019, otherwise (all other years), show all data.
Hopefully someone can help me! 🙂
Try this formula, please.
Wholesale Growth = IF ( ISBLANK ( [Total Wholesale] ); 0; ( [Total Wholesale] - CALCULATE ( [Total Wholesale]; SAMEPERIODLASTYEAR ( Real_date[Date] ) ) ) / CALCULATE ( [Total Wholesale]; SAMEPERIODLASTYEAR ( Real_date[Date] ) ) )
Can you share a small sample? Please mask the sensitive parts first.
What's the formula of [Total Wholesale]?
On the left is my date table and on the right the wholesale table, the 2 are linked on Date = Sale_Date.
The calculation for Total Wholesale
Total Wholesale = calculate(sum('Wholesale'[Wholesale]);'Wholesale'[Segment]<>"3. Other")
It sums the wholesale column and excludes one segment.
Please download a demo from the attachment. All the date like fields should from the Real_Date table.
Thanks for your reply.
The only problem I have is that the formulas dont work when I use the real_date table is steady of the date from the wholesale table.. It returns "empty" when i use:
Total Wholesale YTD = TOTALYTD([Total Wholesale];'Real_date'[Date])
Also, the figure from januari still does not show at Q1. 2019 gives -0,90 while januari gives 0,32 in your example.
I need Q1 and 2019 to say 0,32 as well.
Can you share a similar sample like mine? Please mask the sensitive parts first.
My data is the same as your sample. The problem is that I need 2019(-0.9) and Qtr 1(-0.57) to be 0.32,, just like Januari. It sets Februari and march at 0 when calculating their own value but still take them into account when calculating 2019 and Qtr 1.
Find out who's part of the program this season, and welcome the new Super Users.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Learn about the exciting things that happened in July.
All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.
Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.