cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Value for last business/working day

Good afternoon,

I am looking to work out a value from the last business day,

i.e. if Monday, look back to Friday etc

So far I have the previous days dax as follows:

Boxes put away previous day =
CALCULATE(
SUM('Stock replenishment records'[Box est v2]),
'Stock replenishment records'[Date] = TODAY() - 1)

Can anyone adivse how the dax can calculate and remove weekends to refer to a Friday?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Create  columns.

``weekday=weekday('table'[date],2)``
``weeknum=weeknum('table'[date],2)``

Then create a measure.

``````Boxes put away previous day =
VAR _1 =
CALCULATE (
SUM ( 'Stock replenishment records'[Box est v2] ),
'Stock replenishment records'[Date]
= TODAY () - 1
)
VAR _value =
CALCULATE (
SUM ( 'Stock replenishment records'[Box est v2] ),
'Stock replenishment records'[Date]
= TODAY () - 3
)
RETURN
IF ( 'table'[weekday] = 1, _value, _1 )
``````

If I have misunderstood your meaning, please provide more details withyour desired output and pbix file without privacy infotmaion ( Or some smple data).

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Anonymous
Not applicable

Create  columns.

``weekday=weekday('table'[date],2)``
``weeknum=weeknum('table'[date],2)``

Then create a measure.

``````Boxes put away previous day =
VAR _1 =
CALCULATE (
SUM ( 'Stock replenishment records'[Box est v2] ),
'Stock replenishment records'[Date]
= TODAY () - 1
)
VAR _value =
CALCULATE (
SUM ( 'Stock replenishment records'[Box est v2] ),
'Stock replenishment records'[Date]
= TODAY () - 3
)
RETURN
IF ( 'table'[weekday] = 1, _value, _1 )
``````

If I have misunderstood your meaning, please provide more details withyour desired output and pbix file without privacy infotmaion ( Or some smple data).

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

Good morning,

Anonymous
Not applicable

My fault, I forgot it was a measure and not a column. Please modify the measure again.

``````Boxes put away previous day =
VAR _1 =
CALCULATE (
SUM ( 'Stock replenishment records'[Box est v2] ),
'Stock replenishment records'[Date]
= TODAY () - 1
)
VAR _value =
CALCULATE (
SUM ( 'Stock replenishment records'[Box est v2] ),
'Stock replenishment records'[Date]
= TODAY () - 3
)
RETURN
IF ( max('table'[weekday] )= 1, _value, _1 )``````

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors