Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
WJ876400
Helper IV
Helper IV

Calculation with different data types

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 REVIEWNEXT REVIEWOutcome
23/07/201926/11/201926/11/2019
 To be scheduledTo be scheduled
 To be scheduledTo be scheduled
24/09/2019To be scheduledTo be scheduled
24/09/201925/09/2019To be scheduled
17/06/201924/06/2019To be scheduled
17/06/201910/10/201910/10/2019
01/08/201902/12/201902/12/2019

 

thanks

1 ACCEPTED 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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-lili6-msft
Community Support
Community Support

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:

1.JPG

 

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft 

 

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?

 

thanksCapture.PNGCapture2.PNG

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.