cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Current Month Working days calculation

My objective is to calculate the number of working days up until yesterday for the current month.

The below measure is working in the Desktop version. However, once it is published not calculating in the Web version.

dimCalendar is a table in PowerBI. IsCurrentMonth and IsWeekDay are columns in the dimCalendar table.

CurrentMonthworkingDays =

CALCULATE (

COUNTROWS (dimCalendar),

FILTER (

dimCalendar,

dimCalendar[Day] <= DAY ( TODAY ()-1 )

&& dimCalendar[IsCurrentMonth]=1

&& dimCalendar[IsWeekDay]="True"))

1 ACCEPTED SOLUTION
Super User

@ThusiHettigama Try using a VAR then.

VAR __Day = DAY(UTCTODAY()-1)

RETURN

COUNTROWS(...)

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
Super User

@ThusiHettigama Maybe try UTCTODAY instead of TODAY?

UTCTODAY function (DAX) - DAX | Microsoft Docs

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

Greg,  Thanks for suggesting a solution for my issue. However after using UTCTODAY function, the issue is still occuring

Super User

@ThusiHettigama Hmm, try placing a simple measure like Measure = TODAY() into a Card visualization and see if you get a different value based upon the Desktop or the Service. Wondering if you need to do some kind of TZ conversion.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

Measure = DAY(UTCTODAY()-1) is working in the Desktop version and the Service. However when I add COUNTROWS to calculate number of working days from first day of the current month to DAY(UTCTODAY()-1) it's breaking in the Service (published version)

Super User

@ThusiHettigama Try using a VAR then.

VAR __Day = DAY(UTCTODAY()-1)

RETURN

COUNTROWS(...)

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

@Greg_Deckler Thank you very much for the support. Appreciated !!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors