Forum Discussion

WJ876400's avatar
WJ876400
Helper IV
6 years ago
Solved

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 form...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

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.