Hide Non working days in report filtered by relative date, last 1 day (s)

[Karthik12]

Hello all,

I am working on report to create previous day production output details. 

I have applied a filter -> relative date -> last 1 day as a page filter. 

But, sometimes, report all visuals showing as "Blank" since last day was not working day as per customer plan (example Sunday, Holiday, No plan day) . Showing "Blank" which is not good feeling to customer.

So, I would like to show last production day in which data is there. 

example, today (10-02-2023) and yesterday is non production day. need to show 08-02-2023 data

In customer data structure also if there is no production in the day, that date will not appear. 

Is there any option to sort out?

pbix file attached herewith for reference.

Hide last day to last production day.pbix

[Karthik12]

Hi @MAwwad ,

thanks for the hint.

It little bit tricky to understand. Can you please try my attached report?

[MAwwad]

You can create a calculated column in your data source that identifies working days. You can then use this column in a calculated measure to only show data for working days.

Here's a sample DAX formula for identifying working days:

Working Day = IF(WEEKDAY([Date],2) >= 2 AND WEEKDAY([Date],2) <= 6, 1, 0)

Where [Date] is the name of the date column in your data source. The formula returns 1 for working days (Monday to Friday) and 0 for non-working days (Saturday and Sunday).

Next, you can use this calculated column in a calculated measure to only show data for working days. Here's a sample DAX formula:

Last Working Day Revenue = CALCULATE(SUM(Revenue), FILTER(ALL(Table), Table[Working Day] = 1 && Table[Date] = MAX(Table[Date]) ))

Where Table is the name of your data source, Working Day is the calculated column from above, and Revenue is the column with your revenue data.

After this, you can apply the filter relative date -> last 1 day to your report and it should only show data for the last working day.