Forum Discussion

HetPpatel19's avatar
HetPpatel19
New Member
2 years ago
Solved

Convert Date Column(Data Type: Text) into Date column ( Data Type: Date)

In PowerBI Dashboard, I have create a Matrix Table that summarise all Paid Invoice Total by having "Locations, Project Title, PO#, Invoice#" at Row side, and "ActualPaymentQuarter V1" on Column Side. Current Values is taken from "Sum of InvoicePaidAmount". 

I have also used Timeline visual features from Visualizations to filter the matrix by Days, Weeks, Month, Quarter, and Year. Data is coming from Payment Date Column which is in Original Data. 

My current practise is to maipulate new PaymentDate Column with new revision, which should be convert into QTR, as well as should be used in Matrix. 

But I am not able to change Datatype as a "Date". 

 

 

PaymentDateV1 FormulaPaymentDateTimeline ( Objective Replace PaymentDate Column with NewDateColumn, so Quarterly forecast shows accurate results.DataType Conversion Error

Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, HetPpatel19 

    Thanks for adudani and MNedix positive reply. You can try following dax to solve your problem.


    DAX:

    PaymentDateV2 = 
VAR _paymentDate = 'Table'[PaymentDate]
VAR _estimatedPaymentDate = 'Table'[EstimatedPaymentDate]
VAR _invoicePaidAmount = 'Table'[InvoicePaidAmount]
VAR _documentDate = 'Table'[DocumentDate]

VAR _result = 
IF(
    ISBLANK(_paymentDate) &&
    ISBLANK(_estimatedPaymentDate) &&
    _invoicePaidAmount < 0,
    _documentDate,
    _paymentDate
)
RETURN
DATEVALUE(_result)

     

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum



6 Replies

  • adudani's avatar
    adudani
    Memorable Member
    2 years ago

    hi HetPpatel19 ,

     

    even if you change the datatype to date in the power query editor and load it, do you face the same issue?

    • HetPpatel19's avatar
      HetPpatel19
      New Member
      2 years ago

      Hi, Can we review the new calculated column from Table View in PowerQuery ? 

      • adudani's avatar
        adudani
        Memorable Member
        2 years ago

        hi HetPpatel19 ,

         

        No. Previews of calculated tables cannot be viewed in Power Query.

         

        In order to resolve this issue, in your Calculated table, remove "FORMAT" around the date columns. Then change the data type and format 

  • MNedix's avatar
    MNedix
    Solution Sage
    2 years ago

    Try changing both the type and Locale of the data in Power Query (see below):

     

     

    If this solved your problem then please mark it as the solution so others can see it.

     

    Best,

    • HetPpatel19's avatar
      HetPpatel19
      New Member
      2 years ago

      Thanks for suggation.

      But, When I open PowerQuery I do not see the "PaymentDateV1" column that I have created in TableView by formula(Refer Screenshots). 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, HetPpatel19 

    Thanks for adudani and MNedix positive reply. You can try following dax to solve your problem.


    DAX:

    PaymentDateV2 = 
VAR _paymentDate = 'Table'[PaymentDate]
VAR _estimatedPaymentDate = 'Table'[EstimatedPaymentDate]
VAR _invoicePaidAmount = 'Table'[InvoicePaidAmount]
VAR _documentDate = 'Table'[DocumentDate]

VAR _result = 
IF(
    ISBLANK(_paymentDate) &&
    ISBLANK(_estimatedPaymentDate) &&
    _invoicePaidAmount < 0,
    _documentDate,
    _paymentDate
)
RETURN
DATEVALUE(_result)

     

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum