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.
Hi All,
*** i m working on direct query mode*****
i have as_of_date and day_of_week in my db . i want to create a dax for prior date . Prior date is calculated for the previous day . So, for weekend we need to consider the last week friday date .
Below is the example
as_of_date | DAY_OF_WEEK | Prior date |
5/15/2011 | Sunday | 5/13/2011 |
5/16/2011 | Monday | 5/13/2011 |
5/17/2011 | Tuesday | 5/16/2011 |
5/18/2011 | Wednesday | 5/17/2011 |
5/19/2011 | Thursday | 5/18/2011 |
5/20/2011 | Friday | 5/19/2011 |
5/21/2011 | Saturday | 5/20/2011 |
5/22/2011 | Sunday | 5/20/2011 |
5/23/2011 | Monday | 5/20/2011 |
5/24/2011 | Tuesday | 5/23/2011 |
Kindly help me ..
Thanks
Solved! Go to Solution.
Hello @Anonymous
Add this as a column in your table, should get you what you want.
Prior Date = VAR OffSet = SWITCH ( TRUE(), WEEKDAY('Table'[as_of_date]) = 2, 3, WEEKDAY('Table'[as_of_date]) = 1, 2, 1) RETURN 'Table'[as_of_date] - OffSet
Hello @Anonymous
Add this as a column in your table, should get you what you want.
Prior Date = VAR OffSet = SWITCH ( TRUE(), WEEKDAY('Table'[as_of_date]) = 2, 3, WEEKDAY('Table'[as_of_date]) = 1, 2, 1) RETURN 'Table'[as_of_date] - OffSet
Small change in the requirement :
using the BUSINESS_DAY_IN_MNTH, we need to calculate the Prior date as tomorrow as_of_date and DAY_OF_WEEK will be removed . Could you kindly help me plz..
DAY_OF_WEEK | as_of_date | BUSINESS_DAY_IN_MNTH | DAY_NUM_IN_MNTH | Prior date |
Sunday | 5/15/2011 | 10 | 15 | 5/13/2011 |
Monday | 5/16/2011 | 11 | 16 | 5/13/2011 |
Tuesday | 5/17/2011 | 12 | 17 | 5/16/2011 |
Wednesday | 5/18/2011 | 13 | 18 | 5/17/2011 |
Thursday | 5/19/2011 | 14 | 19 | 5/18/2011 |
Friday | 5/20/2011 | 15 | 20 | 5/19/2011 |
Saturday | 5/21/2011 | 15 | 21 | 5/20/2011 |
Sunday | 5/22/2011 | 15 | 22 | 5/20/2011 |
Monday | 5/23/2011 | 16 | 23 | 5/20/2011 |
Tuesday | 5/24/2011 | 17 | 24 | 5/23/2011 |
You are going to need something in the table to tell you what month the BUSINESS_DAY_IN_MNTH field belongs to.
Sir,
in a year , the BUSINESS_DAY_IN_MNTH are having values from 0 to 365(or any value) . If the day is weekend(sat or sun) or 25th Dec & 1st Jan (hoildays) then we see same number assigned in that BUSINESS_DAY_IN_MNTH. Then we need to populate the prior business working days in to prior date column .
Thanks
thanks a lot ... its working ...
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |