Forum Discussion

PBIBeginner2022's avatar
PBIBeginner2022
Icon for Helper III rankHelper III
4 years ago
Solved

Create a column with only dates of the current month

Hi everyone,     I have this column in my date table with date between 1899 to 2201 :   I need to create an other column in this table with dates of all day in current month. We are in jul...
data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    You can use either of the formulas, second was provided by BA_Pete 

    = if Date.ToText([DateRef],"yyyyMM")=Date.ToText(Date.From(DateTime.FixedLocalNow()),"yyyyMM") then [DateRef] else null
    = if Date.IsInCurrentMonth([DateRef]) then [DateRef] else null

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Then in a custom column named Dates, put following formula

 

= if Date.ToText([DateRef],"YYYYMM")=Date.ToText(Date.From(DateTime.FixedLocalNow()),"YYYYMM") then [DateRef] else null

 

Complete code would be

 

let
    Source = Requête1(#date(1899, 1, 1), #date(2201, 12, 12), null),
    #"Colonne Anee-Mois renommée" = Table.RenameColumns(Source,{{"Annee-Mois", "Mois-Année"}}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne Anee-Mois renommée",{{"DateRef", type date}}),
#"Added Custom" = Table.AddColumn(#"Type modifié", "Dates", each if Date.ToText([DateRef],"YYYYMM")=Date.ToText(Date.From(DateTime.FixedLocalNow()),"YYYYMM") then [DateRef] else null, type date)
in
    #"Added Custom"

 

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

Hi PBIBeginner2022 ,

 

Vijay_A_Verma has provided a working solution for you, but I think the following might be easier:

if Date.IsInCurrentMonth([dateRef]) then [dateRef] else null

 

Pete