Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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 😞
Solved! Go to 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 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 👍 |
Thank you!!! 🙂 it works
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
100 | |
63 | |
45 | |
36 | |
35 |