How to get the previous week in Power Query M

Power Query
1

Hi, need some help with Power Query. I connect PowerBI to Adobe and need to pull data from previous week only. Currently I use the following way to pull data for past 7 days.

        {Cube.ApplyParameter, "DateRange", {DateTime.Date( DateTime.LocalNow() - #duration(7,0,0,0) ), DateTime.Date( DateTime.LocalNow() - #duration(1,0,0,0) )}},

Do you know how to write the code to pull data only from last week?

Thanks!

Mike

2

Hi @Mike,

Give this a go.
It assumes your week starts on a Monday. Don’t know if there are more places in your query where you call DateTime.FixedLocalNow() but you can store that in a a TodaysDate variable for example and call that instead.

{
  Cube.ApplyParameter,
  "DateRange",
  {
    Date.AddWeeks(Date.StartOfWeek(DateTime.FixedLocalNow(), Day.Monday), - 1),
    Date.StartOfWeek(DateTime.FixedLocalNow(), Day.Sunday)
  }
}

I hope this is helpful.

1 Like
3

Hi, @Melissa, thank you very much.

If I publish my Dashboard and set up the scheduled refresh at the weekly basis, will the codes give me the data from the previous week every time? For example, if it is run today, it will give me the week of last week. If the dashboard refreshed in April 15th, it will give the previous week data based on April 15th?

Thanks!

Mike

4

Hi @Mike,

Paste this code into a new blank query. It creates a list of dates for the previous week based on the TodaysDate variable. When you schedule a refresh and the M code get’s evaluated make sure you use the Date.From( DateTime.FixedLocalNow()) instead. The hard coded date below is only to demonstrate the expeced outcome for any given date, as you can see I’ve now passed April 15th.

let
    TodaysDate = #date(2021, 4, 15), //Date.From( DateTime.FixedLocalNow()),
    LastWeek = List.Transform({ Number.From(Date.AddWeeks( Date.StartOfWeek( TodaysDate,Day.Monday),-1))..Number.From(Date.StartOfWeek(TodaysDate,Day.Sunday))}, Date.From )
in
    LastWeek

I hope this is helpful.

5

@Melissa, thanks Melissa, I keep getting one error, but not sure what caused the problem. Here is the code to connect to Adobe.

let
Source = AdobeAnalytics.Cubes([HierarchicalNavigation=true]),
#“Added Items” = Cube.Transform(myadobereport,
{
{Cube.ApplyParameter, “DateRange”, {Date.AddWeeks(Date.StartOfWeek(DateTime.FixedLocalNow(), Day.Monday), - 1), Date.StartOfWeek(DateTime.FixedLocalNow(), Day.Sunday)}},
{Cube.AddAndExpandDimensionColumn, “DateGranularity”, {“year”, “month”, “day”}, {“Date Granularity.Level 1: Year”, “Date Granularity.Level 2: Month”, “Date Granularity.Level 3: Day”}},
{Cube.AddMeasureColumn, “Visits”, “visits”}
})
in
#“Added Items”

And here is the error message:

Expression.Error: We cannot convert the value #datetime(2021, 3, 1, 0, 0, 0) to type Date.
Details:
Value=2021-03-01 12:00:00 AM
Type=[Type]

image
image1118Ă—132 4.27 KB

From the error message, it is possible to know where the problem came from? Thank you very much for your help!

Mike

6

Okay so a date type seems to be required where a datetime type is passed.
Give this a go.

let
    Source = AdobeAnalytics.Cubes([HierarchicalNavigation=true]),
    #"Added Items" = Cube.Transform(myadobereport,
        {
            {Cube.ApplyParameter, "DateRange", { Date.From(Date.AddWeeks(Date.StartOfWeek(DateTime.FixedLocalNow(), Day.Monday), - 1)), Date.From(Date.StartOfWeek(DateTime.FixedLocalNow(), Day.Sunday))}},
            {Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
            {Cube.AddMeasureColumn, "Visits", "visits"}
        }
    )
in
    #"Added Items"

.
I hope this is helpful

7

Hi @Melissa, You are awesome! That works!!!

Thanks tons!

Mike

8

Hi @Mike, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like