cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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:

 

DateSalesLabelYearFY Year
2020-01-013A2020FY 2020
2020-02-014A2020FY 2020
2020-01-013B2020FY 2020
2020-02-014B2020FY 2020
2019-03-015A2019FY 2019
2019-02-011A2019FY 2019
2018-02-013A2018FY 2018
2018-04-012A2018FY 2018
2019-03-015B2019FY 2019
2019-02-011B2019FY 2019
2018-02-013B2018FY 2018
2018-04-012B2018FY 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:

 

LabelThis Year SalesPrevious Year Sales
A65
B65

 

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 4
Highlighted
Super User I
Super User I

Re: Time intelligence getting lost on custom columns created from the date columns

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...

Highlighted
Community Support
Community Support

Re: Time intelligence getting lost on custom columns created from the date columns

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.

1.PNG

2.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Advocate I
Advocate I

Re: Time intelligence getting lost on custom columns created from the date columns

@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))

Highlighted
Advocate I
Advocate I

Re: Time intelligence getting lost on custom columns created from the date columns

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!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors