Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |