I have a measure which calculates the number of working days within the current month (up to yesterday) with the following calculation:
Working Days = CALCULATE(SUM(Holidays[Working Day]), FILTER(Holidays, Holidays[Date] >= ([First date in slicer])), FILTER(Holidays, Holidays[Date] <= [Latest Date in slicer]))
The Holiday's DB is stored online and has 3 columns, Day (this is a text field for an explanation of why a week day isn't a working day e.g. Christmas day etc.), Date (formatted in UK format) and Working Day (either 1 or 0) whereby 1 is a working day and 0 isn't.
The first date in slicer is the first of the current month: DATE(YEAR(TODAY()), MONTH(TODAY() -1), 1)
The latest date in slicer is yesterday (as we run the report for the last full day which will always be the day before it's ran: TODAY() - 1
When I run it in Power BI desktop, I get working days as 4. When I upload it and run it on Power BI Online, I get working days as 5. This then impacts a whole table as the table is divded by the number of working days.
Can anyone help? I have changed the Power BI online language settings to be default already and that has updated the date formats on the slicers from US to UK but it is still saying 5 working days and not 4.
We've added two new DAX functions this month that help if you're working with date-time data across timezones. DAX has long supported the NOW() and TODAY() functions that return time and date in the timezone that the function's being used - so if a .pbix file is passed to someone in a different timezone they'll see different results. UTCNOW() and UTCTODAY() will always return the current time or date in UTC so you can guarantee consistent results wherever you are (and also when you upload the workbook to the Power BI service).
Community Support Team _ Xiaoxin Sheng If this post helps, please consider Accept it as the solution to help the other members find it more quickly Learning resources: Power BI