Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Prior date of the week

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_dateDAY_OF_WEEKPrior date
5/15/2011Sunday5/13/2011
5/16/2011Monday5/13/2011
5/17/2011Tuesday5/16/2011
5/18/2011Wednesday5/17/2011
5/19/2011Thursday5/18/2011
5/20/2011Friday5/19/2011
5/21/2011Saturday5/20/2011
5/22/2011Sunday5/20/2011
5/23/2011Monday5/20/2011
5/24/2011Tuesday5/23/2011

 

Kindly help me ..

 

Thanks

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User
Super User

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
Anonymous
Not applicable

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_WEEKas_of_dateBUSINESS_DAY_IN_MNTHDAY_NUM_IN_MNTHPrior date
Sunday5/15/201110155/13/2011
Monday5/16/201111165/13/2011
Tuesday5/17/201112175/16/2011
Wednesday5/18/201113185/17/2011
Thursday5/19/201114195/18/2011
Friday5/20/201115205/19/2011
Saturday5/21/201115215/20/2011
Sunday5/22/201115225/20/2011
Monday5/23/201116235/20/2011
Tuesday5/24/201117245/23/2011

 

You are going to need something in the table to tell you what month the BUSINESS_DAY_IN_MNTH field belongs to.

Anonymous
Not applicable

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 

Anonymous
Not applicable

thanks a lot ... its working ...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.