Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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()))
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()))
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 : )
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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |