I Have a date Column in my direct query data, i want to add a dax which will show previous work Data

[Amar-Agnihotri]

I Have a date Column in my direct query data, i want to add a dax which will show previous working day data that means if today is monday it should show friday's data and if today is tuesday it should monday's data, also if it is sunday it should show friday's data only. I can't add column measure as direct query doesn't allow that. How to do this ?

Thanx in advance

[Syndicate_Admin]

Hi  @Amar-Agnihotri ,

Create 2 columns as below:

Is working day = IF(WEEKDAY('Table'[Date],2)=6||WEEKDAY('Table'[Date],2)=7,0,1)

previous work Data = 
var _maxdate=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Is working day]=1))
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]=_maxdate))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[v-kelly-msft · Accepted solution]

Hi  @Amar-Agnihotri ,

Create 2 columns as below:

Is working day = IF(WEEKDAY('Table'[Date],2)=6||WEEKDAY('Table'[Date],2)=7,0,1)

previous work Data = 
var _maxdate=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Is working day]=1))
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]=_maxdate))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[huey999]

Hi, You need to another column to your date table - PreviousWorkingDay and populate it with the DateDimID (YYYYMMDD integer) of the PreviousWorkingDay. 

Then, create a calculation as below

PremiumAmount_PriorWorkday_total:= CALCULATE ([Totalbyworkingday],

FILTER (

ALL ('Date'),

'Date'[DateDimID]=max('Date'[PreviousWorkDay])

)

there's an article on SQLBI for the same sort of calculations. You may need to search a bit to find it as I cant remember the name.