Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone!
I have a P&L in Power BI that looks like the below picture where all values are in YTD.
Two issues I am currently facing:
When choosing a specific month from the date slicer (i.e. February 2022), my “Last Year” values (February 2021) disappear. Is there a way to fix this and keep showing the parallel period values?
Also, when choosing more than one month in the slicer, my values are aggregated (i.e. February Net Profit Ratio shows as the sum of Jan and Feb NP Ratios). Any way to reflect the true ratios without aggregation?
Your help is greatly appreciated!
My desired output is the following table:
Thanks in advance,
Peter
Solved! Go to Solution.
HI @Peter_Yaacoub,
#1, This should relate to the filter effect, the filter effect will also apply to your table based on the relationship key to limit the calculation ranges. For this scenario, you can create an unconnected table as a source of the slicer and write DAX expression to extract the selection and use it as conditions to calculate with your formula.
#2, The aggregation feature is the basic part of visual usage, you can't prevent them on visual effects. If you want to show the specific records, you can do an aggregate(e.g. max, min) on your selection results to only get one value.
Regards,
Xiaoxin Sheng
HI @Peter_Yaacoub,
#1, This should relate to the filter effect, the filter effect will also apply to your table based on the relationship key to limit the calculation ranges. For this scenario, you can create an unconnected table as a source of the slicer and write DAX expression to extract the selection and use it as conditions to calculate with your formula.
#2, The aggregation feature is the basic part of visual usage, you can't prevent them on visual effects. If you want to show the specific records, you can do an aggregate(e.g. max, min) on your selection results to only get one value.
Regards,
Xiaoxin Sheng
@Peter_Yaacoub , how are you creating last year ytd?
example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
or
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
Hi Amit,
Thank you for your response.
My values are in YTD and I calculated my last year Sales as follows:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |