Create Measure Displaying Data from Previous Working Day

[Jamesfindog]

Hi All,

I have a Calendar table with the previous working day already implemented as a column:

I am looking to create a measure to show only a value which relates to that day, as such:

PreviousDayEmployees = CALCULATE([UniqueEmployees],
                                               applicable_date = LastWorkDay)

I also tried to create a measure to use in place of LastWorkDay, as below:

PrevWorkingDay1 = CONVERT(CALCULATE(
MAX('Calendar Master Table'[LastWorkDay]),
'Calendar Master Table'[LastWorkDay] < TODAY()), DATETIME)

This seems to function as intended.

I then created a CALCULATE statement:
PrevWorkingDayEmployees = CALCULATE(
[Unique Employees],
FILTER('MSite FILO', 'MSite FILO'[applicable_date] = [PrevWorkingDay1]))

However, this is returning no values.

Any insight?

Cheers!

[v-achippa]

Hi @Jamesfindog,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ahmedx for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue is resolved? 

If not, please provide sample data that covers your issue or question completely. And please show the expected outcome based on the sample data you provided.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Jamesfindog,

We wanted to kindly follow up to check if your issue is resolved? If not, please provide sample data that covers your issue or question completely. And please show the expected outcome based on the sample data you provided.

Thanks and regards,

Anjan Kumar Chippa

[Ahmedx]

Pls try 

PrevWorkingDayEmployees = CALCULATE(
[Unique Employees],
FILTER(ALL('MSite FILO'), 'MSite FILO'[applicable_date] = [PrevWorkingDay1]))

---------------
PrevWorkingDayEmployees = CALCULATE(
[Unique Employees],
'MSite FILO'[applicable_date] = [PrevWorkingDay1])

and you should have a data type here [applicable date] = DATETIME
I don't understand why you need to convert the data here to DATETIME.
PrevWorkingDay1 = CONVERT(...., DATETIME)
 

[Jamesfindog]

Hi mate,

Still no results (there are definitely results).

I have removed the conversion.

[Ahmedx · Accepted solution]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers?https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...