Do you have to have a date column to use time intelligence functions?

[Anonymous]

Hi,

I am trying to use time intelligence functions to eventually work out MTD, YTD, YOY Actuals and Percentages. My data looks like the following:

Data Source

MM	YYYY	Description Level1	Description Level2	Revenue	Volume	CustomerID	Segment
1	2017	Shirts	Small	10	1	123	Youth
1	2017	Trousers	Medium	15	2	124	Youth
1	2017	Shirts	Small	10	1	125	Middle
1	2017	Trousers	Large	25	2	126	Old
1	2017	Shirts	Large	30	3	127	Middle

I created a date table and created a relationship between Year on the date table and YYYY in the data source. However, when I try to use functions like SAMEPERIODLAST YEAR or DATEADD it does not work. Do I need to have an actual date column in order to use time intelligence functions?
The data spans to 2021.

Regards,

Dr_Watcher

[Anonymous]

@amitchandak @selimovd thanks I have created the date column and a relationship between that column and the date column in the date table. However, I still ran into an issue.

I think created the following measures:

Total Volume = SUM('Data Source'[VOLUME])

Total Volume LY = CALCULATE([Total Volume], SAMEPERIODLASTYEAR('Date Table'[Date]))

However, its still not working as the table below shows Total Volume = Total Volume LY

[selimovd]

Hey @Anonymous ,

can you show a picture of the date table and the connection?

The date table has to have single days, in the table you post I don't see a proper date like 01.01.2021

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi @selimovd  see below:

Date Table:

Relationship:

Thanks
Dr_Watcher

[selimovd]

Hey @Anonymous ,

your measure [Total Volume] ist the SUM('Data Source'[VOLUME]), the relationship you show is between your date table and the table 'All - Copy'.

How does the relationship between the date table and the table 'Data Source' look like?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[amitchandak]

@Anonymous , I think you need to create a date in table and join that with date table.

Create a date like

Date = Date([Year], [month],1)

[selimovd]

Hey @Anonymous ,

all the time intelligence functions work only if you have a proper date table that needs each day of the year in the specific time frame.

So if you create a proper date table and connect it to your table, then the time functions will work.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic