Forum Discussion

ashmitp869's avatar
ashmitp869
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

Create date fields provide in the file - Date From to Date to .?

Hi,

Can I generate a date column from [Date From] until [Date To] in Power BI.

Similar like below image

I have create for 2024 04

I am getting the date range from [Date From] to [Date To]

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi ashmitp869, like this?

     

    Before

     

    After

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MFTSUTLWNzDUB3GBbEMzOCdWB6bICCRhjqTK2ACLKnOghBFQlTFMlaW+gQlUUSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, #"Date From" = _t, #"Date To" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date From", type date}, {"Date To", type date}}),
    Ad_Dates = Table.AddColumn(ChangedType, "Dates", each List.Dates([Date From], Duration.TotalDays([Date To] - [Date From]) +1, #duration(1,0,0,0)), type list),
    ExpandedDates = Table.ExpandListColumn(Ad_Dates, "Dates")
in
    ExpandedDates

     

2 Replies

  • Elcin_7's avatar
    Elcin_7
    Icon for Helper I rankHelper I
    2 years ago

    Hi, 

    Option 1 - Power Query- Add Column-Custom Column use the formula below

     

    =Date.AddDays([Date], 13)

     

    Option 2 - create a column in data panel and use the DAX formula below. If it necessary use the relevant table and column names from your data

     

    Date to=
    SWITCH(
        WEEKDAY('tablename'[Date from] + 1, 1),7, 'tablename'[Date from]  +13,  
            'tablename'[Date from]  +13
    )
    This option is conditional you can change the formula depending your weekday ect. In your example you need 13 days in any condition. 
  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi ashmitp869, like this?

     

    Before

     

    After

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MFTSUTLWNzDUB3GBbEMzOCdWB6bICCRhjqTK2ACLKnOghBFQlTFMlaW+gQlUUSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, #"Date From" = _t, #"Date To" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date From", type date}, {"Date To", type date}}),
    Ad_Dates = Table.AddColumn(ChangedType, "Dates", each List.Dates([Date From], Duration.TotalDays([Date To] - [Date From]) +1, #duration(1,0,0,0)), type list),
    ExpandedDates = Table.ExpandListColumn(Ad_Dates, "Dates")
in
    ExpandedDates