locked
Previous month data adds on 1st day of current month RRS feed

  • Question

  • Hi All, hope you can help with this issue i am having - 

     

    I have created a "Period Table" within PowerBi that holds the date values as shown on below code:

     

    let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
    {"Today", 
    TodaysDate, 
    TodaysDate, 
    1},
    {"Current Week To Date", 
    Date.From(Date.StartOfWeek(TodaysDate)), 
    TodaysDate, 
    2},
    {"Last 10 days", 
    Date.AddDays(TodaysDate,-10) + #duration(1,0,0,0), 
    TodaysDate, 
    3},
    {"Current Month To Date", 
    Date.From(Date.StartOfMonth(TodaysDate)), 
    TodaysDate, 
    4},
    {"Current Year To Date", 
    Date.From(Date.StartOfYear(TodaysDate)), 
    TodaysDate, 
    5},
    {"Rolling Week", 
    Date.AddWeeks(TodaysDate,-1) + #duration(1,0,0,0), 
    TodaysDate, 
    6},
    {"Rolling Month", 
    Date.AddMonths(TodaysDate,-1) + #duration(1,0,0,0), 
    TodaysDate, 
    7},
    {"Last Month", 
    Date.AddMonths(Date.From(Date.StartOfMonth(TodaysDate)),-1),Date.StartOfMonth(TodaysDate), 
    8}
    },
    GetTables = List.Transform(Ranges, 
    each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)

    in
    Output

     

    I have some problems with the "Last Month" section - i want to show all previous month data but it adds on the 1st day of the current month

     

     

    Please can you advise what im doing wrong on the code:

     

    {"Last Month", 
    Date.AddMonths(Date.From(Date.StartOfMonth(TodaysDate)),-1),Date.StartOfMonth(TodaysDate), 
    8}

     

    Thank You

    Monday, March 20, 2017 9:19 AM

Answers

  • I have some problems with the "Last Month" section - i want to show all previous month data but it adds on the 1st day of the current month

    "Last month" returns two values - The first day of the previous month: Date.AddMonths(Date.From(Date.StartOfMonth(TodaysDate)), -1), and the first day of the current month: Date.StartOfMonth(TodaysDate).

    So do you want a list of all days in the previous month, or only the first and last day of the previous month?

    First and last day of previous month:

    {"Last Month", Date.AddMonths(Date.StartOfMonth(TodaysDate), -1), Date.StartOfMonth(TodaysDate) - #duration(1,0,0,0), 8}

    All days of previous month:

    {"Last Month", List.Transform({Number.From(Date.AddMonths(Date.StartOfMonth(TodaysDate), -1))..Number.From(Date.StartOfMonth(TodaysDate) - #duration(1,0,0,0))}, Date.From), 8}


    Monday, March 20, 2017 2:17 PM