Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
Could you pleae help me to create Last4 weeks dax formula. Am getting below error while trying to create DAX for Last4Weeks data.
Thnaks in advance
Thanks
Narasimha Reddy
If you are using a dimdate based from a sql table you can create a column in the dimension to calculate the weeks from the current date as an integer. We have a few week counters for the different ways to count weeks. The datediff in powerbi itself is limited in that it will not count ngeative date differences.
SELECT dd.* , IIF(DATEPART(YEAR, GETDATE()) - 1 <= dd.year, 1, 0) AS 'Last2Years' , DATEDIFF(WEEK, dd.start_date_of_week, xx.StartDate) AS 'WeeksFromCurrentday' , DATEDIFF(WEEK, dd.iso_start_date_of_week, xxx.StartDate) AS 'WeeksFromCurrentdayISO' , DATEDIFF(WEEK, dd.bybox_start_date_of_week, xxxx.StartDate) AS 'WeeksFromCurrentdayByBox' FROM dim_date_dsv AS dd OUTER APPLY ( SELECT MAX(dsvx.start_date_of_week) AS 'StartDate' FROM dbo.dim_date_dsv AS dsvx WHERE dsvx.sql_date = CONVERT(DATE, GETDATE()) ) AS xx OUTER APPLY ( SELECT MAX(dsvx.iso_start_date_of_week) AS 'StartDate' FROM dbo.dim_date_dsv AS dsvx WHERE dsvx.iso_year = YEAR(DATEADD(DAY, ( 4 - DATEPART(WEEKDAY, GETDATE()) ), GETDATE())) AND dsvx.iso_week_of_year = DATEPART(iso_WEEK, GETDATE()) - 1 ) AS xxx OUTER APPLY ( SELECT MAX(dsvx.bybox_start_date_of_week) AS 'StartDate' FROM dbo.dim_date_dsv AS dsvx WHERE dsvx.sql_date = CONVERT(DATE,GETDATE()) ) AS xxxx;
Once you have this it's relatviely easy to set a new column in power bi to filter, we use 13 weeks but you can easily chnage to 4.
The following column is on our dimdate table in powerbi to use in filters.
Last 13 Weeks ByBox = if(Dim_Date[WeeksFromCurrentdayByBox]<=13 && Dim_Date[WeeksFromCurrentdayByBox]>0,"Last 13 Weeks",if(Dim_Date[WeeksFromCurrentdayByBox]=0,"Current Week","> 13 Weeks"))
Using the error message it's fairly easy to repair the calculation. All you need to do is to makes sure the Date in a current row is not greater than the date returned by TODAY() function:
Last4Weeks1 = IF(DimDate[Date]>TODAY();"NO";IF(DATEDIFF(DimDate[Date];TODAY();WEEK)<4 && WEEKNUM(DimDate[Date])<>WEEKNUM(TODAY());"YES";"NO"))
Thanks for quick response. am getting below result while applying last4weeks data dax forumala. seems it is worng results.
Below is the correct output . below visual created manually with correct created/Closed data.
Hi @Narasimha,
Which DAX did you write to return Closed and Created values? Can you share some sample data and logic to return expected results?
Best Regards,
Qiuyun Yu
Below is sample data. I want last 4 weeks created/Closed like below. below output is created from excel data manualy entered week values.
below is sample data.
ID | StartDate | Enddate |
323223223 | 6/16/17 2:13 AM | 6/29/17 7:39 AM |
323223224 | 6/14/17 9:13 PM | 6/29/17 7:39 AM |
323223225 | 6/29/17 7:26 AM | 6/29/17 7:29 AM |
323223226 | 6/29/17 6:56 AM | 6/29/17 6:58 AM |
323223227 | 6/17/17 10:20 PM | 6/29/17 6:42 AM |
323223228 | 6/22/17 10:10 PM | 6/29/17 6:40 AM |
323223229 | 6/22/17 5:47 AM | 6/29/17 6:40 AM |
323223230 | 6/22/17 5:47 AM | 6/29/17 6:40 AM |
323223231 | 6/22/17 4:39 AM | 6/29/17 6:40 AM |
323223232 | 6/22/17 5:33 AM | 6/29/17 6:38 AM |
323223233 | 6/29/17 6:21 AM | 6/29/17 6:25 AM |
323223234 | 6/29/17 5:51 AM | 6/29/17 5:54 AM |
323223235 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223236 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223237 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223238 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223239 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223240 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223241 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223242 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223243 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223244 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223245 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223246 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223247 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223248 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223249 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223250 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223251 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223252 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223253 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223254 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223255 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223256 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223257 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223258 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223259 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223260 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223261 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223262 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223263 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223264 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223265 | 6/28/17 10:46 AM | 6/29/17 5:33 AM |
323223266 | 6/29/17 5:21 AM | 6/29/17 5:24 AM |
323223267 | 6/28/17 11:53 PM | 6/29/17 5:20 AM |
323223268 | 6/28/17 11:53 PM | 6/29/17 5:20 AM |
323223269 | 6/28/17 11:53 PM | 6/29/17 5:20 AM |
323223270 | 6/28/17 11:53 PM | 6/29/17 5:20 AM |
323223271 | 6/28/17 11:53 PM | 6/29/17 5:20 AM |
323223272 | 6/28/17 11:53 PM | 6/29/17 5:20 AM |
323223273 | 6/28/17 11:53 PM | 6/29/17 5:20 AM |
323223274 | 6/28/17 11:53 PM | 6/29/17 5:20 AM |
323223275 | 6/29/17 4:46 AM | 6/29/17 4:48 AM |
323223276 | 6/29/17 4:14 AM | 6/29/17 4:17 AM |
323223277 | 6/29/17 3:41 AM | 6/29/17 3:44 AM |
323223278 | 6/29/17 3:13 AM | 6/29/17 3:16 AM |
323223279 | 6/24/17 2:57 AM | 6/29/17 2:47 AM |
323223280 | 6/29/17 2:41 AM | 6/29/17 2:43 AM |
323223281 | 6/29/17 2:16 AM | 6/29/17 2:18 AM |
323223282 | 6/29/17 1:46 AM | 6/29/17 1:48 AM |
323223283 | 6/29/17 1:12 AM | 6/29/17 1:14 AM |
323223284 | 6/29/17 12:37 AM | 6/29/17 12:40 AM |
323223285 | 6/29/17 12:02 AM | 6/29/17 12:05 AM |
323223286 | 6/28/17 7:08 PM | 6/28/17 11:46 PM |
323223287 | 6/28/17 7:16 PM | 6/28/17 11:46 PM |
323223288 | 6/20/17 5:14 PM | 6/28/17 11:46 PM |
323223289 | 6/28/17 11:27 PM | 6/28/17 11:29 PM |
323223290 | 6/28/17 10:57 PM | 6/28/17 10:59 PM |
323223291 | 6/28/17 10:22 PM | 6/28/17 10:24 PM |
323223292 | 6/28/17 9:57 PM | 6/28/17 9:59 PM |
323223293 | 6/2/17 9:15 AM | 6/28/17 9:38 PM |
323223294 | 6/28/17 9:22 PM | 6/28/17 9:25 PM |
323223295 | 6/28/17 8:52 PM | 6/28/17 8:54 PM |
323223296 | 6/28/17 3:11 PM | 6/28/17 8:42 PM |
323223297 | 6/28/17 8:17 PM | 6/28/17 8:19 PM |
323223298 | 6/28/17 7:47 PM | 6/28/17 7:49 PM |
323223299 | 6/28/17 7:17 PM | 6/28/17 7:19 PM |
323223300 | 6/28/17 6:42 PM | 6/28/17 6:44 PM |
323223301 | 6/28/17 6:11 PM | 6/28/17 6:13 PM |
323223302 | 6/20/17 12:28 AM | 6/28/17 5:36 PM |
323223303 | 6/28/17 5:32 PM | 6/28/17 5:34 PM |
323223304 | 6/28/17 5:02 PM | 6/28/17 5:05 PM |
323223305 | 6/28/17 10:44 AM | 6/28/17 4:38 PM |
323223306 | 6/28/17 11:49 AM | 6/28/17 4:38 PM |
323223307 | 6/28/17 10:52 AM | 6/28/17 4:38 PM |
323223308 | 6/28/17 10:36 AM | 6/28/17 4:38 PM |
323223309 | 6/28/17 10:36 AM | 6/28/17 4:38 PM |
323223310 | 6/28/17 9:48 AM | 6/28/17 4:38 PM |
323223311 | 6/28/17 4:27 PM | 6/28/17 4:30 PM |
323223312 | 6/28/17 3:57 PM | 6/28/17 4:00 PM |
323223313 | 6/23/17 2:44 AM | 6/28/17 3:46 PM |
323223314 | 6/28/17 3:22 PM | 6/28/17 3:24 PM |
323223315 | 6/28/17 2:52 PM | 6/28/17 2:55 PM |
323223316 | 6/28/17 2:17 PM | 6/28/17 2:19 PM |
323223317 | 6/27/17 3:16 PM | 6/28/17 2:08 PM |
323223318 | 6/28/17 1:42 PM | 6/28/17 1:45 PM |
323223319 | 6/28/17 1:12 PM | 6/28/17 1:15 PM |
323223320 | 6/28/17 11:40 AM | 6/28/17 11:57 AM |
323223321 | 6/2/17 1:07 PM | 6/28/17 11:57 AM |
323223322 | 6/28/17 11:22 AM | 6/28/17 11:25 AM |
Thanks
Narasimha
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |