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.
My data is in the following format:
Date Value
01/01/2020 100.00
01/01/2020 12.55
31/01/2020 50.00
02/02/2020 95.65
02/02/2020 1256.00
08/02/2020 62.55
I want to return the following in a matrix:
Current Date Previous Date
01/01/2020
31/01/2020 01/01/2020
02/02/2020 31/01/2020
08/02/2020 02/02/2020
I have the following measure written for Previous Date but I am getting a blank:
Previous Date =
VAR a =
MAX ( 'Lift Contract History'[Date] )
VAR b =
CALCULATE (
MAX ( 'Lift Contract History'[Date] ),
FILTER ( 'Lift Contract History', 'Lift Contract History'[Date] < a )
)
RETURN
b
Am I doing something obviously wrong? Many thanks for all help
Solved! Go to Solution.
does it work? it seems ok from the first sight
also you can try to use all()
Previous Date =
VAR a =
MAX ( 'Lift Contract History'[Date] )
VAR b =
CALCULATE (
MAX ( 'Lift Contract History'[Date] ),
FILTER ( ALL('Lift Contract History'), 'Lift Contract History'[Date] < a )
)
RETURN
b
Hi @ansa_naz ,
Create the index column in the query editor first.
Get the previous date by index:
Measure = var current_index =SELECTEDVALUE('Table'[Index])
var p_date = CALCULATE(FIRSTNONBLANK('Table'[date],1),FILTER(ALL('Table'),'Table'[Index]=current_index-1))
return p_date
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ansa_naz ,
You can create a Calculated Column,
does it work? it seems ok from the first sight
also you can try to use all()
Previous Date =
VAR a =
MAX ( 'Lift Contract History'[Date] )
VAR b =
CALCULATE (
MAX ( 'Lift Contract History'[Date] ),
FILTER ( ALL('Lift Contract History'), 'Lift Contract History'[Date] < a )
)
RETURN
b
Thanks @az38 adding in the ALL did the trick, cant believe i forgot to do that! You da man!
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |