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.
Hi,
I have 2 tables that are connected via the YearID field:
Fact_Sales:
YearID
Product
Sales
Dim_Year:
YearID
Year
I have a Year filter in the Filter panel.
I want to display the Product wise sales for the selected year and the previous year on a bar chart.
Since the data granularity is at Year level, I cannot use PARALLELPERIOD() or SAMEPERIODLASTYEAR() to calculate the sales for the previous year.
how can I calculate the sales for the previous year?
Thank you @Jihwan_Kim , @amitchandak This worked
There is a slight change in my requirement:
I have 2 tables that are connected via the Year_Quarter field:
Fact_Sales:
YearID
QuarterID
Year_Quarter
Product
Sales
Dim_Year:
YearID
Year
QuarterID
Year_Quarter
I have 2 filters in the Filter panel: Year, Quarter
I want to display the Product wise sales for the selected year, quarter combination and same quarter previous year on a bar chart.
how can I calculate the sales for the same quarter previous year?
Hi, @vanessa
Please try the below measure.
Or, please share your sample pbix file's link here, then I can try to look into it, and come up with a more accurate measure.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim thank you very much, but the measure doesn't seem to work.
Also, mutilple years and quarters can be selected from the filter panel, so the logic with 'Max' won't work, right?
Hi, @vanessa
Thank you very much for your feedback.
I think, in general, it should work.
Perhaps, I missed something from your table structure.
Please try the below, or please share your sample pbix file's link here, then I can try to come up with a more accurate solution.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @vanessa
Please check the below picture and the sample pbix file's link down below.
Sales Total Previous Year =
CALCULATE (
Sales[Sales Total],
FILTER ( ALL ( 'Year' ), 'Year'[Year] = MAX ( 'Year'[Year] ) - 1 )
)
https://www.dropbox.com/s/l9q4i8k7r9cfpb5/vanessa.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@vanessa , Try like
This Year = CALCULATE(sum('Fact_Sales'[sales]),filter(ALL('Dim_Year'),'Dim_Year'[Year]=max('Dim_Year'[Year])))
Last Year = CALCULATE(sum('Fact_Sales'[sales]),filter(ALL('Dim_Year'),'Date'[Dim_Year]=max('Dim_Year'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |