Table Repeat value based on other column

[PatCreator]

Hi All,

I want to create a calculation "Baseline" like the one below that contains the 1st value from [Pop Coverage] col and its repeated though the whole [Year] Col.

please help, I've spent so many hours 😞

[ghoshabhijeet · Accepted solution]

@PatCreator  Thanks for your query.

Here is my solution:

1. Create a calculate column for Date in the table using the below DAX:

Date = 
VAR MonthNum = SWITCH( 
TRUE(),
'DataTable'[Month] = "January",1,
'DataTable'[Month] = "February",2,
'DataTable'[Month] = "March",3,
'DataTable'[Month] = "April",4,
'DataTable'[Month] = "May",5,
'DataTable'[Month] = "June",6,
'DataTable'[Month] = "July",7,
'DataTable'[Month] = "August",8,
'DataTable'[Month] = "September",9,
'DataTable'[Month] = "October",10,
'DataTable'[Month] = "November",11,
'DataTable'[Month] = "December",12
)
VAR Result = DATE( 'DataTable'[Year], MonthNum , 1)

RETURN
Result

Table Snapshot:

2. Create a measure for Baseline:
Baseline =
VAR MinDate =
CALCULATE (
MIN ( 'DataTable'[Date] ),
ALL ( 'DataTable' ),
'DataTable'[Year] = SELECTEDVALUE ( 'DataTable'[Year] )
)
VAR Result =
CALCULATE (
MIN ( 'DataTable'[Pop Coverage] ),
ALL ( 'DataTable' ),
'DataTable'[Year] = SELECTEDVALUE ( 'DataTable'[Year] ),
'DataTable'[Date] = MinDate
)
RETURN
Result

Output Snapshot:

Hope this helps.

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

[PatCreator]

Thank you!!! 🙂 it works

[ghoshabhijeet]

@PatCreator You are welcome 😀👍