Calculated Column Last work day

[Anonymous]

Hi All,

I want to pull out the transaction for any vehicle on a given report based on previous workday for a selected week  ( monday to sunday)

In this instance last work day would be 1st Oct, but for that vehicle there's no transaction on 1st oct, the report pulled out a day befoe 30/09 Monday. Problem is i only want the 'Previous' day hence 1st Oct, below is what i have.

4 table

1) Date table a calculated column. History/Not, =  If(Today<Date, 'History', 'No')

2) Another Calculated column History workday, If(History/Not="", "", Date)

3) Measure 'lastnon blank' of the 'History workday, to give me the last work day of the 'History'.

4) Did a Calculated measure,

Calculate(LastLocation, Filter(Location, Location[Date] = [Previousworkdate]

Can i please know what is the problem ? Why is it pulling out 30/9 instead of 1/10(nothing on 1/10 and so i expect it to be blank)

[dax]

Hi vinc,

If possible, could you please inform me more detailed information(such as your sample data and expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

Hi,

Thanks for helping.

Please find the report attach. cheeers

Report

[dax]

Hi vinc,

I didn't find LastLocation in your sample, please  check it and inform me more detailed information(such as your expected output)? Then I will help you more correctly.

Best Regards,
Zoe Zhi

[Anonymous]

Hi Zoe,

can  you please assist ?

thanks

[Anonymous]

Hi

is the one i have problem with.

calculate firstnonblank for the Day = CALCULATE([Firstnonblank job or location],FILTER(PlantGPS,PlantGPS[Date]=[PreviousWorkday]))

I expect when previousWorkday = PlantGPS Date then use PlantGPS data if not then return LocationReport[Address])

But now when PlantGPS(date) <>Previousworkday, it will use the previous day of Previousworkday (ie if previous workday is 6/10/2019 and no PlantGPSdate = 6/10/2019, it will use 5/10/2019).