March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi
I have the below table, I have put what data type they are above each header. What I want to achieve is the outcome column, the outcomes are below. I am struggling with the different date formats
If Last Review is blank = To be scheduled
If Next Review - Last Review is <=30 = To be scheduled
If Next review - Last Review is >30 then use the date in next review
DATE/TIME (dd/MM/yyyy) | TEXT | |
LAST REVIEW | NEXT REVIEW | Outcome |
23/07/2019 | 26/11/2019 | 26/11/2019 |
To be scheduled | To be scheduled | |
To be scheduled | To be scheduled | |
24/09/2019 | To be scheduled | To be scheduled |
24/09/2019 | 25/09/2019 | To be scheduled |
17/06/2019 | 24/06/2019 | To be scheduled |
17/06/2019 | 10/10/2019 | 10/10/2019 |
01/08/2019 | 02/12/2019 | 02/12/2019 |
thanks
Solved! Go to Solution.
hi, @WJ876400
The column is going in the values of the matrix table because of the way it is contsructed if that is causing an issue
Column in matrix value will be aggregated, this should be the reason for it.
And this formula is used for a calculated column, so it will work in data table, You may adjust the formula as a measure.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
If you still have problem, please share your sample pbix file for us have a test.
Regards,
Lin
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"LAST REVIEW", type datetime}, {"NEXT REVIEW", type any}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [NEXT REVIEW]="To be scheduled" then "To be scheduled" else if Number.From([NEXT REVIEW]) - Number.From([LAST REVIEW])<30 then "To be scheduled" else [NEXT REVIEW]) in #"Added Custom"
Hope this helps.
hi, @WJ876400
For your case, just try this formula to create a column
Column = IF ( 'Table'[LAST REVIEW] = BLANK (), "To be scheduled", IF ( 'Table'[NEXT REVIEW] = "To be scheduled", "To be scheduled", IF ( DATEDIFF ( 'Table'[LAST REVIEW], DATEVALUE ( 'Table'[NEXT REVIEW] ), DAY ) > 30, FORMAT ( 'Table'[NEXT REVIEW], "dd/mm/yyyy" ), "To be scheduled" ) ) )
Result:
here is sample pbix file, please try it.
Regards,
Lin
thanks for the help. I have added in a column and put in the formula but it doesn't seem to work for certain fields. It is not putting to be scheduled where the last review is blank and the next review is to be scheduled. It is also not using the next review date when the next review is 30 days greater than the last review. The column is going in the values of the matrix table because of the way it is contsructed if that is causing an issue?
thanks
hi, @WJ876400
The column is going in the values of the matrix table because of the way it is contsructed if that is causing an issue
Column in matrix value will be aggregated, this should be the reason for it.
And this formula is used for a calculated column, so it will work in data table, You may adjust the formula as a measure.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
If you still have problem, please share your sample pbix file for us have a test.
Regards,
Lin
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
128 | |
90 | |
75 | |
56 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |