Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
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! 🙂
Hi @MxJ,
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] ) ) )
Best Regards,
I have tried it but unfortunatly it does not work. It sets Q2, 3, 4 on zero but still uses jan, feb, mar for Q1 2018 and only jan for Q1 2019.
Hi @MxJ,
Can you share a small sample? Please mask the sensitive parts first.
What's the formula of [Total Wholesale]?
Best Regards,
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.
Hi @MxJ,
Please download a demo from the attachment. All the date like fields should from the Real_Date table.
Best Regards,
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.
Hi @MxJ,
Can you share a similar sample like mine? Please mask the sensitive parts first.
Best Regards,
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.
Hi @MxJ,
I'm afraid this is what it should be. Let's have a look. The 2019-January means 2019-Jan compares 2018-Jan. The 2019-Qtr 1 means 2019-Qtr 1 compares 2018-Qtr 1. Though the value of 2019-Qtr 1 only has one month, 2018-Qtr 1 has three months value.
Then there is a question. How can we know that the 2019-February doesn't have data or it should be 0 (the result is 0)?
Best Regards,
Hi @v-jiascu-msft !
Sorry for my late response, I was on holiday. I guess you are right. I think I will exclude the current year and make a extra button "2019" that will go to a bookmark that shows the same table but with figures per month instead of per quarter, then the numbers do make sense.
If you have a better option though, please let me know! 🙂
I have tried it but unfortunatly it does not work. It sets Q2, 3, 4 on zero but still uses jan, feb, mar for Q1 2018 and only jan for Q1 2019.