Linking Time Intelligence to Text

[PowerBIoverExcl]

Dear all,

I am breaking my head over the following topic. I have a date table and use time intelligence DAX formulas to calculate quarterly sales growth. Although that is working fine, I want to make my formula compatible with a time series notation stored as text.

First of all, please find the data structure as shown below:

The DimDate table is my date table, and I have linked the 'FullDateKey' to the Date column in the Customer table. This enables the time intelligence formulas to work. However, selecting a specific quarter or day alone with the Date column is not sufficient for my analysis, as the there are several date notations in the 'Time Period'  column which use the same day notation in the Date Column.

Example: For quarter Q3-2022, I use the date 1st July 2022 in the Date column, but I have data for that day for 2022 Full Year, LEQ1 Q3, LEQ2 Q3 and Q3 Actuals in the Time Period column (which is in text format). Hence, just filtering only with the Date column aggregates data across different time period notations in the Time Period column and results in a bogus result. I need to filter the dataset further to have PowerBI only work with one specific Time Period as well. Unfortunately, when doing so I get empty columns:

I do not know why, because the Time Period and Date columns are both present in the Customer table. Ideally, the user only selects a time period in the Time Period column, which is linked to the Date column and which would enable PowerBI to use time intelligence formulas. Can anyone help me link the Time Period column to the Date column, or know any other way forward? 🙏

[PowerBIoverExcl]

Hi @Greg_Deckler, thanks for the links. I think I am in need of a bit more tailored solution.

To start off, I calculate the sales in last quarter using the following formula: 

Sales LQ =  CALCULATE([Sum of Sales], DATEADD(Customer[Date],-1,QUARTER))

As mentioned in my initial post, this formula works fine on its own, but I need it to comply with the Time Period column. How can I manage this? When looking at the links I was referred to this post but I don't see a way forward yet: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-NEXTDAY-Lone-Biker-of-the-Apoc....

Thanks for the support

[Greg_Deckler]

@PowerBIoverExcl You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

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