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.
I'm a newbie to the Power BI platform. Please forgive me if this is too naive but really request for help on the below. The data is in the below format:
Date | Sales | Label | Year | FY Year |
2020-01-01 | 3 | A | 2020 | FY 2020 |
2020-02-01 | 4 | A | 2020 | FY 2020 |
2020-01-01 | 3 | B | 2020 | FY 2020 |
2020-02-01 | 4 | B | 2020 | FY 2020 |
2019-03-01 | 5 | A | 2019 | FY 2019 |
2019-02-01 | 1 | A | 2019 | FY 2019 |
2018-02-01 | 3 | A | 2018 | FY 2018 |
2018-04-01 | 2 | A | 2018 | FY 2018 |
2019-03-01 | 5 | B | 2019 | FY 2019 |
2019-02-01 | 1 | B | 2019 | FY 2019 |
2018-02-01 | 3 | B | 2018 | FY 2018 |
2018-04-01 | 2 | B | 2018 | FY 2018 |
The 'FY Year' is a custom column. Now, I want a slicer on 'FY Year' & a table showing current year sales & previous year sales. But when I use 'Year' on the slicer, everything works fine but If I use 'FY Year' on a slicer, previous year sales column turn blank.
The required table if the 'FY Year' is selected as 2019 can be seen below:
Label | This Year Sales | Previous Year Sales |
A | 6 | 5 |
B | 6 | 5 |
The formulas used to create the measures are:
This Year Sales = SUM(Sales)
Previous Year Sales = CALCULATE(SUM(Sales), SAMEPERIODLASTYEAR(Date))
Please assist on how we can handle this.
Thank you!
Hi @aditya_fractal ,
Just modify your "Previous Year" measure as below.
Previous Year Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLEXCEPT('Table','Table'[Label]),'Table'[FY Year]="FY "&RIGHT(SELECTEDVALUE('Table'[FY Year]),4)-1))
Result would be shown as below.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-jayw-msft ,
Thank you so much for your reply. This works flawlessly. However, I'm looking for some generic solution so that it works for the custom columns i.e. 'FY Year', 'Quarter' which will have values Q1 2017, Q2 2017, etc.. & for 'Semester' which will have values as 'H1 2017', 'H2 2017', etc...
Following the approach told by you works very well but for that, I would have to create different measures for FY, Semester & Quarter. Please let me know if you have any idea how we can create a single measure to cater to the above scenario.
As multiple measures, would increase the complexity as per my requirement.
Thank you!
Time Intelligence can be complicated so not a naive question.
There are reasons why the Previous Year measure is not returning anything but I won't go into them. However, this will fix it.
Get yourself a Date table which covers the range of dates that you have in your table. Create a relationship between your table and the Date table. Remove the Year and FY Year from your table. Create them in the date table.
Use the FY Year column from the Date table in the slicer.
Change the Previous Year measure to use the Date column from the Date table.
Time Intelligence always works properly with a date table. Well, not always...
@HotChilli Thank you so much for your reply. This methodology worked to show values in the 'previous year sales' column but I can see some issues here. I request you to help me with resolving that. I followed the steps as mentioned below:
Now, the values are very well appearing in the Previous Year Sales. The issue I'm able to see over here is that the 'This year sales' & 'previous year sales' are not matching for some of the years. One of the possible reason I want to explain as follow. Please let me know if my understanding is correct
Request you to review the above & please let me know how we can handle this.
Thank you!
This Year Sales = SUM(Sales)
Previous Year Sales = CALCULATE(SUM(Sales), SAMEPERIODLASTYEAR(Date))
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |