Subtracting 2 business days from a date column

DAX DAX Calculations
1

See attached, just want to subtract 2 business dates from the date column excluding week-ends?

Naveena File.pbix (17.1 KB)

1 Like
2

@FPastor ,

Please check out the following video. It goes through both my preferred DAX and Power Query approaches to solving this problem.

I think this will get you exactly what you need, but if you still have questions feel free to give a shout.

– Brian

1 Like
3

Thanks Bryan, onto it straight away

FP 1967

4

Hi @FPastor, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

Hi Enterprise DNA,

It helped me to solve it with my own calendar table.

Regards,

F Pastor

1 Like
6

@FPastor ,

Glad to hear that was helpful. Just curious - did you end up going the DAX or the Power Query route to solve this?

  • Brian
7

@BrianJ ,

I created, in Power Query a conditional column with week-day and week-end, based on this my friend was able to DAX it with dateadd -2 and wrap it up in a IF statement based but I am not 100% aware of how he managed or if this is the best solution but as far as I know she managed it.

Thanks once more for the help.

F Pastor.
PS The Extended Data table is massive ( super complete) but massive…I use the initial parameter as below and then in Power Q build the table with just what I need based on the report I am creating.

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}})
in
RenamedColumns

in
fnDateTable