How to implement Time intelligence on custom columns/measures?

[aditya_fractal]

Hi all,

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.

I have a date column which is a date hierarchy containing

• Year
• Month
• Quarter

I need to show up, Year, total sale, total previous year sale, etc. I'm able to do this using CALCULATE & SAMEPERIODLASTYEAR function.

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 & the functions used above are not working anymore.

Please let me know if there is any other way of showing up FY2020, FY2019, etc.. & if this is the only way, how can I handle to show the required matrix?

Thanks in advance!

[Greg_Deckler]

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

[aditya_fractal]

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

[amitchandak]

This FY & year is in date table ?

And you marked it as date table. Also, Make sure slicer is from date table column.

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

[Greg_Deckler]

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

[aditya_fractal]

@Greg_Deckler @amitchandak 

I apologize for the delayed response. Please allow me to describe the situation as 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!