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 everyone,
I have a table as below and try to find solution for a matching problem. I would like to get the same value in [Value] for each month in [Date], if [Date] in the year 2019. This mean, all rows with [Date] = 01.01.2019 will get [Value] = 100, and all rows with [Date] = 01.02.2019 will get [Value] = 250, and so on. [Date] of 2018 will stay in the data.
My initial approaches are LOOKUPVALUE(), and CALCULATE() together with ALLEXCEPT(). However they don't work.
So I hope that I can receive any idea from you.
Thank you and wish all you a nice christmas time!
Solved! Go to Solution.
Hi @Anonymous ,
If i understand you correctly, EARLIER() function might be helpful for you.
Please refer to the measure below and see if the result achieve your expectation.
Column =
IF (
FORMAT ( 'Table'[date], "YYYY" ) = "2019",
CALCULATE (
MAX ( 'Table'[value] ),
FILTER (
'Table',
FORMAT ( 'Table'[date], "YYYYMM" )
= FORMAT ( EARLIER ( 'Table'[date] ), "YYYYMM" )
)
),
'Table'[value]
)
Result would be show as below:
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If i understand you correctly, EARLIER() function might be helpful for you.
Please refer to the measure below and see if the result achieve your expectation.
Column =
IF (
FORMAT ( 'Table'[date], "YYYY" ) = "2019",
CALCULATE (
MAX ( 'Table'[value] ),
FILTER (
'Table',
FORMAT ( 'Table'[date], "YYYYMM" )
= FORMAT ( EARLIER ( 'Table'[date] ), "YYYYMM" )
)
),
'Table'[value]
)
Result would be show as below:
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear @v-jayw-msft ,
Thank you so much for your immediate and wonderful support!
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 |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |