Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ...
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |