Forum Discussion
Values show as Sum and Disappear from Table
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!
Power BI Table
My desired output is the following table:
Thanks in advance,
Peter
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
3 Replies
- amitchandak
Super User
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-bd52912a5bd4Hi Amit,
Thank you for your response.
My values are in YTD and I calculated my last year Sales as follows:
Last Year Net Sales = CALCULATE(SUM(UK_Profit_and_Loss[Net Sales]),UK_Profit_and_Loss[Scenario]="Last Year").The issue is that values are showing as aggregates if choosing any month other than January. I have provided a screenshot of my current model in case I am missing something there.Thanks for the help and glad to have given a sub on YT.Best,Peter
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
