cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StefanM Regular Visitor
Regular Visitor

Display this week & this month data

Hello everyone!

 

I am new to PBI and already have a couple of very interesting things working. I am currently trying to setup a page that tracks some percentages in a table based on a static -never changing- date range. Those would be:

 - Today & Yesterday,
 - This Week & Last Week,
 - This Month & Last Month,
 - This Year & Last Year 
 - Historical

 

I already have Today & Yesterday working based off a video I saw online. But after around a day of attempts, I just cannot figure out how to get Weeks working.

My columns are:

 - Date, (Date Data Type)
 - Month Name, (Text Data Type)
 - Week of Year, (Text Data Type)
 - Today Or Yesterday, (Created in Data View)
 - This Week or Last Week (Created - sort of at least- in the Data View)

Are there any wizards out there that could help put a Dax string together for something like this?

 

Much appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Display this week & this month data

So normally this would look something like this:

 

Column = 
VAR __week = WEEKNUM([Date])
VAR __weekToday = WEEKNUM(TODAY())
VAR __lastweek = __weekToday - 1
RETURN
IF(__weekToday = __week,"This Week",IF(__lastweek = __week,"Last Week",BLANK()))

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

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: Display this week & this month data

So normally this would look something like this:

 

Column = 
VAR __week = WEEKNUM([Date])
VAR __weekToday = WEEKNUM(TODAY())
VAR __lastweek = __weekToday - 1
RETURN
IF(__weekToday = __week,"This Week",IF(__lastweek = __week,"Last Week",BLANK()))

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

Proud to be a Datanaut!


StefanM Regular Visitor
Regular Visitor

Re: Display this week & this month data

Hi @Greg_Deckler

 

Fantastic timing, I just got it working another way, but your method is so much more intuitive for me that I think I will do that from now on!

 

For reference, these are the other strings im using for Month & Week atm:

MonthTest = 
    VAR __month = MONTH([Date])
    VAR __monthToday = MONTH(TODAY())
    VAR __lastmonth = __monthToday - 1
    RETURN
            IF(__monthToday = __month,"This Month",IF(__lastmonth = __month,"Last Month",BLANK()))

IsInLastWeek2 = 
    if(
        (WEEKNUM(NOW())-1) = [Week Of Year],"Last Week",
            if(
                (WEEKNUM(NOW())) = [Week Of Year],"This Week","Other"))

Curious that those two set the week starting on different days (top is Sunday, bottom is Monday). Monday is actually my preference due to the project, is there anyway to change that in the string?

I also got days working like this:

DateChecker = 
var datecheck = FORMAT('Star'[Date], "DD/MM/YYYY")
var istoday =FORMAT(NOW(), "DD/MM/YYYY") 
var isyesterday =FORMAT(NOW()-1, "DD/MM/YYYY") 
return

if(
    istoday = datecheck, "Today", 
    if(isyesterday = datecheck, "Yesterday", 
    "Other"
))

This is all working so far, looking great! Thanks for all the help so far : )

StefanM Regular Visitor
Regular Visitor

Re: Display this week & this month data

I dont exactly understand why atm, perhaps ive been glaring at my screen for too long... but the following amendment updated it so that Monday was the begining of the week:

 

VAR __week = WEEKNUM([Date],2)

 

Super User
Super User

Re: Display this week & this month data

There are a couple of switches for WEEKNUM that change when a week starts and ends:

 

https://docs.microsoft.com/en-us/dax/weeknum-function-dax

 

Return type: 1, week begins on Sunday. Weekdays are numbered 1 through 7.

Return type: 2, week begins on Monday. Weekdays are numbered 1 through 7.


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

Proud to be a Datanaut!