Forum Discussion

AnaIsa's avatar
AnaIsa
Frequent Visitor
2 years ago
Solved

calculate date before

Hi, 

How I can calculate a previous date  since a column with a data time received. Example: I have a column with this date:

 

I want to calculate a previous date, it would be 31/03/24

 

thank you

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, AnaIsa 

    Thanks for lbendlin reply. This is an additional step to his response, if you are confused how to proceed, you can refer to the following methods.

    1. Power Query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDRNzDUNzIwMlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"ChangedType" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    AddedCustom = Table.AddColumn(ChangedType, "Previous Day", each Date.AddDays([Date], -1), type date)
in
    AddedCustom


    2. Power BI Desktop

     

    Previous Day 2 = 
VAR _previousDate = CALCULATE(SELECTEDVALUE('Table'[Date]) - 1)
RETURN
_previousDate

    Or use Dateadd function directly.
    DATEADD - DAX Guide

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, AnaIsa 

    Thanks for lbendlin reply. This is an additional step to his response, if you are confused how to proceed, you can refer to the following methods.

    1. Power Query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDRNzDUNzIwMlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"ChangedType" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    AddedCustom = Table.AddColumn(ChangedType, "Previous Day", each Date.AddDays([Date], -1), type date)
in
    AddedCustom


    2. Power BI Desktop

     

    Previous Day 2 = 
VAR _previousDate = CALCULATE(SELECTEDVALUE('Table'[Date]) - 1)
RETURN
_previousDate

    Or use Dateadd function directly.
    DATEADD - DAX Guide

    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

    • AnaIsa's avatar
      AnaIsa
      Frequent Visitor
      2 years ago

      I would like to see the sales value for previous month at product detail, I only see it in a general table like this:

      but if I want to see by detail, If I select a month I see the same amount for AC and AC-1, the formula used for AC-1 is: 

      Transit AC-1 = CALCULATE([Total Transit],(DATEADD('Date'[Date],-1,MONTH)))

       

      could you help me please ?

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    In Power Query you subtract #duration(1,0,0,0) . In DAX you subtract 1.