Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi ,
I m new to power bi ,I have below dataset. In Target 2 column , blank values filled with previous value.Please help me to write dax for same.
Year-Week | TARGET | TARGET2 |
2020-25 | 14112 | 14112 |
2020-26 | 14112 | |
2020-27 | 10872 | 10872 |
2020-28 | 10872 | |
2020-29 | 10872 | |
2020-30 | 8928 | 8928 |
Thanks
Solved! Go to Solution.
@Anonymous
You can use the below Measure, I only changed the EARLIER function to MAX.
Measure_Filldown =
VAR LNBT =
CALCULATE(MAX('Table'[YEAR-WEEK]),
FILTER(
ALL('Table'),
'Table'[YEAR-WEEK]<=MAX('Table'[YEAR-WEEK]) && 'Table'[TARGET]<>BLANK()
)
)
RETURN
CALCULATE(
MAX([TARGET]),
'Table'[YEAR-WEEK] = LNBT ,ALL('Table')
)
Did I answer your question? Mark my post as a solution!
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
Try this as a new column.
VAR LNBT =
CALCULATE(MAX('Table'[YEAR-WEEK]),
FILTER(
ALL('Table'),
'Table'[YEAR-WEEK]<=EARLIER('Table'[YEAR-WEEK]) && 'Table'[TARGET]<>BLANK()
)
)
RETURN
CALCULATE(
MAX([TARGET]),
'Table'[YEAR-WEEK] = LNBT ,ALL('Table')
)
Did I answer your question? Mark my post as a solution!
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hey,
Thanks its working. I m trying same formula in measure but its giving error.
Can you tell me reason we couldnt use this formula in measure.
Thanks
@Anonymous
Evaluation context in Column (Table) and Measure are different in this case.
Thanks
Did I answer your question? Mark my post as a solution!
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
You can use the below Measure, I only changed the EARLIER function to MAX.
Measure_Filldown =
VAR LNBT =
CALCULATE(MAX('Table'[YEAR-WEEK]),
FILTER(
ALL('Table'),
'Table'[YEAR-WEEK]<=MAX('Table'[YEAR-WEEK]) && 'Table'[TARGET]<>BLANK()
)
)
RETURN
CALCULATE(
MAX([TARGET]),
'Table'[YEAR-WEEK] = LNBT ,ALL('Table')
)
Did I answer your question? Mark my post as a solution!
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi ,
I m getting one strange issue. I have connected DATA table with Date dimension and pulling Year-Week from date table. Its showing incorrect result when i sort measure2 based year-week order,otherwise its showing correct result.
Using below formula
Hi @Anonymous
I tested with the measure that I shared and it works, please check the attached file.
https://1drv.ms/u/s!AmoScH5srsIYgYFdHeVAE8V-SZzFFQ?e=d1YwV3
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
I tested with your measure in my PBI,its not showing same result.
@Anonymous
Where do you do the sorting?
The measure works on the Ascending Order of YEAR-WEEK. Also, I gave the solution based on your original requirement where you mentioned a single table with blank values. The MEASURE2 works fine, which is a modification to the measure I shared.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Right, your measure is working absolutely correct and I have modified you're measure in change data table. Only issue is when I sort by Year-Week it's working fine but when I sort Year-Week order column then it's showing wrong result
Hello @Anonymous,
Do you want this to be resolved in Power Query or DAX function? Please let me know.
DAX Measure
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |