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, looking to compare grades between selected date from filter and selected date -1.
Eg of table contents
Company Date Grade
ABC 19-01-2020 Red
ABC 20-01-2020 Amber
XYZ 19-01-2020 Green
XYZ 20-01-2020 Amber
XYZ 21-01-2020 Red
If I filtered on Date = 20-01-2020, the desired output in a Table visualisation should look like this
Company Date Grade Prev_date Prev_date_Grade
ABC 20-01-2020 Amber 19-01-2020 Red
XYZ 20-01-2020 Amber 19-01-2020 Green
Can one help?
Solved! Go to Solution.
Hi @Anonymous ,
This is my sample table:
Based on my test, 2020/1/21 is Tuesday, it will return the right result using previous formula:
My sample file is attached: Create measure to return text from selected date.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You can create these two measures:
Prev_date = SELECTEDVALUE('Table'[Date]) -1
Prev_Date_Grade =
CALCULATE (
MAX ( 'Table'[Grade] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
= SELECTEDVALUE ( 'Table'[Date] ) - 1
&& 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] )
)
)
You will get your hoped result like this:
Here is my sample file that hopes to help you, please check and try it: Create measure to return text from selected date.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, just realised I need to adapt this to exclude non working days so if selected date was a Monday then date -1 should refer to previous Friday, bonus if we can also exclude UK bank holidays.
table
Company Date Grade
ABC 17-01-2020 Red
ABC 20-01-2020 Amber
XYZ 17-01-2020 Green
XYZ 20-01-2020 Amber
XYZ 21-01-2020 Red
If selected date = 20-01-2020 then output to look like below
Company Date Grade Prev_date Prev_date_Grade
ABC 20-01-2020 Amber 17-01-2020 Red
XYZ 20-01-2020 Amber 17-01-2020 Green
Can you help?
Hi @Anonymous ,
You can modified the two measures what I have first posted like this:
Prev_date =
VAR _pre =
SELECTEDVALUE ( 'Table'[Date] )
RETURN
IF ( WEEKDAY ( _pre, 2 ) = 1, _pre - 3, _pre - 1 )
Prev_Date_Grade =
CALCULATE (
MAX ( 'Table'[Grade] ),
FILTER (
ALL ( 'Table' ),
(
'Table'[Date]
= SELECTEDVALUE ( 'Table'[Date] ) - 3
|| 'Table'[Date]
= SELECTEDVALUE ( 'Table'[Date] ) - 1
)
&& 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] )
)
)
You will get your expected result:
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Almost there. I'm testing this on my table with 000's of records. It seems if company XYZ has entry on Tuesday, it refers back to previous Friday (date-3) than Monday (date-1). I think the OR statement below needs refining.
Hi @Anonymous ,
This is my sample table:
Based on my test, 2020/1/21 is Tuesday, it will return the right result using previous formula:
My sample file is attached: Create measure to return text from selected date.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try something like
Noncontinous Date
Last Grade = CALCULATE(Max('Table'[Grade]),filter(all(Table),Table[Date] =MAXX(FILTER(all(Table),Table[Date]<max(Table[Date])),Table[Date])))
continuous Date
Last Grade = CALCULATE(Max('Table'[Grade]),dateadd(Date[Date],-1,Day))
Prefer to use date table in place table[Date],
like
Last Grade = CALCULATE(Max('Table'[Grade]),filter(all(Date),Date[Date] =MAXX(FILTER(all(Date),Date[Date]<max(Date[Date])),Date[Date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
@Anonymous try this measure, As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
Prev Date Value =
CALCULATE ( MAX ( Table[Grade] ), ALLEXCEPT ( Table, Table[Company] ),
DATEADD( DateTable[Date], -1, DAY )
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |