Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
OwenAuger
Super User
Super User

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

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

 

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

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
vanessafvg
Super User
Super User

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?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.