How to implement Time intelligence on custom columns/measures?

Question

Hi all,&nbsp;This might be pretty easy for most of us around here but I'm a newbie to the POWER BI platform. Request your help for the below scenario.&nbsp;I have a date column which is a date hierarchy containingYearMonthQuarterI need to show up, Year, total sale, total previous year sale, etc. I'm able to do this using CALCULATE &amp; SAMEPERIODLASTYEAR function.&nbsp;But the slicer for Year should show values as FY2020, FY2019, FY2018 for which I've to create a new measure which has resulted in the loss of Time intelligence &amp; the functions used above are not working anymore.&nbsp;Please let me know if there is any other way of showing up FY2020, FY2019, etc.. &amp; if this is the only way, how can I handle to show the required matrix?&nbsp;Thanks in advance!&nbsp;

greg_deckler · Answer

A new measure or column? Can you just create a new column in your table, "FY" &amp; [Year] and use that in your slicer?

aditya_fractal · Answer

Greg_DecklerYes, I'm having "FY"&amp;"[Year]" as a column in the table. That's working fine on the slicer but the time intelligence functions do not work anymore.

greg_deckler · Answer

That's weird. Can you post sample data and formulas? Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

amitchandak · Answer

This FY &amp; year is in date table ?
And you marked it as date table. Also, Make sure slicer is from date table column.
&nbsp;
Totalytd or datesytd should work
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

aditya_fractal · Answer

Greg_Deckler&nbsp;amitchandak&nbsp;I apologize for the delayed response. Please allow me to describe the situation as below.The data is in the below format:&nbsp;DateSalesLabelYearFY Year2020-01-013A2020FY 20202020-02-014A2020FY 20202020-01-013B2020FY 20202020-02-014B2020FY 20202019-03-015A2019FY 20192019-02-011A2019FY 20192018-02-013A2018FY 20182018-04-012A2018FY 20182019-03-015B2019FY 20192019-02-011B2019FY 20192018-02-013B2018FY 20182018-04-012B2018FY 2018&nbsp;The 'FY Year' is a custom column. Now, I want a slicer on 'FY Year' &amp; a table showing current year sales &amp; 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 SalesA65B65The formulas used to create the measures are:This Year Sales = SUM(Sales)Previous Year Sales = CALCULATE(SUM(Sales), SAMEPERIODLASTYEAR(Date))&nbsp;Please assist on how we can handle this.&nbsp;Thank you!

Forum Discussion

How to implement Time intelligence on custom columns/measures?

5 Replies

Related content

Error in Microsoft Applied Skills: Implement a Real-Time Intelligence Assessment Lab : Need help

Measure two columns

MDM implementation

Implementing Fabric Apps by Rayfin SDK

Help with Date intelligence