Forum Discussion
Time intelligence getting lost on custom columns created from the date columns
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!
4 Replies
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:
- I've created a new date table, created the required FY Year column.
- Created a relationship b/w the date table & the data table (where the data is stored previously).
- Changed the measure to point out to date table now
- Changed the slicer to point to the 'FY Year' in the date table
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
- If 'FY 2018' has dates from say January 1st, 2018 to March 1st, 2018, and
- If 'FY 2017' has dates from say February 1st, 2017 to April 1st, 2017
- When I select FY 2018
- Current Year sales will be on sum of values from January 1st. 2018 to March 1st, 2018
- Previous Year sales will be on the sum of values from January 1st, 2017 to March 1st, 2017 as per the measure created i.e. using SamePeriodLastYear function. But as we have the data from Feb 1st, 2017 to April 1st, 2017, the measure is not including the data after March 1st, 2017
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))
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 Anonymous ,
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!
