Cards to show data for the last week, last month, etc based on current date...

[Matty]

Hi All,

Just started using Power BI in anger and I really like the software. 

I'm building a report at the moment where I would like cards to show data for the last week, last month, etc., but I'm struggling to establish a way of doing this without a manual filter.

As I want my report to be dynamic based on the current date, last week should show data from week commencing 10/04, but how can I do this in Power BI?

This would be a breeze for me in Excel, but I'm still learning Power BI and DAX formulas, hence the post.  Hope someone has a suggestion as to how the described problem could be resolved.

Cheers,

Matty

[OwenAuger]

Hi @Matty

Since you are wanting to filter on dates relative to the current date (rather than relative to the date filtered), such as last week and last month, there is an approach you could use covered in these two blog posts:

https://gqbi.wordpress.com/2016/09/07/create-dynamic-periods-for-fiscal-or-calendar-dates-in-power-b...

https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi...

These approaches use a table which relates Ranges of Dates to Period Names, and use bidirectional cross-filtering to filter your Calendar table.

The good thing about this method is that you don't need to modify the DAX in your measures. However, all of your Periods are relative to a fixed date (updated at time of refresh).

For example, this table would contain rows like this, with the Date column on the many side of a 1:many bi-directional relationship with the Calendar table:

Period           Date

Previous Week    10-Apr-17

Previous Week    11-Apr-17

Previous Week    12-Apr-17

Previous Week    13-Apr-17

Previous Week    14-Apr-17

Previous Week    15-Apr-17

Previous Week    16-Apr-17

Previous Month   1-Mar-17

Previous Month   2-Mar-17

...

Anyway, have a play with these method or post back if that wasn't what you were looking for.

Cheers,

Owen 🙂

[Matty]

Hi Owen,

Thanks for the links - very interesting reading!

I can see what's going on and I've managed to implement it to my project.  I am, however, struggling to understand the syntax of the 'M' language, but I will persevere.

One thing I can't fathom is why you would maintain a 'Date' table that is hard coded in terms of a start and end date (I can see a start date quoted and then 730 days is added to determine the end date).  Surely it would be better for this table to be dynamic in terms of reflecting what dates are shown in the main table?  Or am I missing something?

Cheers,

Matty

[Matty]

Hi,

Playing around with the M code, I've cobbled together the following to return dates from the previous week (based on today being the current day):

 {"Last Week", 
 Date.From(Date.StartOfWeek(Date.AddDays(TodaysDate,-7))), 
 Date.EndOfWeek(Date.AddDays(TodaysDate,-7)), 
 2},

But it does seem a bit heavy to achieve the desired result.  Is there a more succinct way?

Cheers,

Matty

[v-ljerr-msft]

Hi @Matty,

Another way to show data for the last week, last month, etc based on current date, is using DAX to add the follow calculate columns(Year, MonthNo, WeekNo, etc) in your Calendar table or the Fact table first.

Year = YEAR('Date'[Date])

MonthNo = MONTH('Date'[Date]) 

WeekNo = WEEKNUM('Date'[Date])

Then you should be able to simply use the formulas below to show data for the last week, last month based on current date.

Last Week Sales =
VAR currentYear =
    YEAR ( TODAY () )
VAR currentWeekNo =
    WEEKNUM ( TODAY () )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( Date, Date[Year] = currentYear && Date[WeekNo] = currentWeekNo - 1 )
    )

Last Month Sales =
VAR currentYear =
    YEAR ( TODAY () )
VAR currentMonthNo =
    MONTH ( TODAY () )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( Date, Date[Year] = currentYear && Date[MonthNo] = currentMonthNo - 1 )
    )

Regard

[OwenAuger]

Hi @Matty

Agree - it's best if the date table adjusts to cover the dates elsewhere in the model, rather than hard-coding a date range.

On your other question, I'm not sure if the Date.From is strictly required, as Date.StartOfWeek and Date.EndOfWeek both appear to return type date.

I'm not sure if there's any alternative to the logic you've used for last week's start/end dates.

You could make the code a little more readable like this with a "local variable":

let StartOfLastWeek = Date.StartOfWeek(Date.AddDays(TodaysDate,-7))
in
{
  "Last Week", 
  StartOfLastWeek
  Date.AddDays(StartOfLastWeek, 6), 
  2
},

Someone else may have a better idea.

Cheers,

Owen

[vanessafvg]

i would think you would need to create calculated measures for each one of those

have a look at the previous functions here  

https://msdn.microsoft.com/en-us/library/ee634763.aspx

do you want them all displayed at once, or should a user need to navigate through them?