cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Matty Helper II
Helper II

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

Hi All,

 

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

 

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

6 REPLIES 6
Super User I
Super User I

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

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?

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
OwenAuger Community Champion
Community Champion

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

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 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Matty Helper II
Helper II

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

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 Helper II
Helper II

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

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

 

OwenAuger Community Champion
Community Champion

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

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Microsoft v-ljerr-msft
Microsoft

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

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. Smiley Happy

 

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors