Value for last business/working day

[ajaydavidluke]

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?

Many thansk in advance.....

[Anonymous · Accepted solution]

Hi @ajaydavidluke ,

Please have a try.

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.

[ajaydavidluke]

Good morning,

I have followed your suggestion but I receive the following error:

[Anonymous]

Hi @ajaydavidluke ,

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.