Latest values based on dates stored as text in summary table

[Saulring]

Hi all, 

I am looking at creating a summary table, where I have a number of different measures and data comes in quarterly (for some measures annually). The dates are stored as text - "2023/24 Q1".

In the summary table I want to show: Measure, Latest date,Latest value, Previous value, Difference, and Direction of Travel. What would be a better way to go about this? An addition of index column and base the calculations on that, VS creating a date table and establish relationship? Below is my table example and as you can see some measures do not have quarterly data, only annual. Many thanks in advance.

measure	date	value	Quarter
smoking	2023/24	15	1
smoking	2023/24	16	2
smoking	2023/24	11	3
drinking	2023/24	10	1
drinking	2023/24	20	2
driving	2023/24	100
driving	2024/25	111
smoking	2023/24	50	4
drinking	2023/24	30	3

[amitchandak · Accepted solution]

@Saulring , You can get a date like

Date new= eomonth(date(left([date],4), 3*right([date],1), 1),0)

Now you can use date table and Time Intelligence

examples

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

You can also consider offset function or Visual calculations 

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

Power BI Visual Calculations with Offset, Index, Rank, RowNumber| February 2024 Update:
https://www.youtube.com/watch?v=JwMF0endTPY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L