cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

## Working days and Days gone

Hi everyone,

I have a table as below, which:

- DATE DE is from individual Calendar table

- ATMONTH_ and ATLDF_ are from another table (which date in this table has already relationship with calendar table)

And then I did 2 calculations for working days and number of days gone to display total working days and days gone automatically every day as below:

daymonths = VAR Day_ = IF( DAY(TODAY()) < 10 , "0" & FORMAT(DAY(TODAY()),"") , FORMAT(DAY(TODAY()),""))
VAR Month_ = IF( MONTH(TODAY()) < 10 , "0" & FORMAT(MONTH(TODAY()),"") , FORMAT(MONTH(TODAY()),""))
VAR Year_ = FORMAT(YEAR(TODAY()),"")
Return
Calculate ( MAX ( FAC_Workdays[ATMONTH_] ),
'Date'[DATE DE] = Day_ & "." & Month_ & "." & Year_
)
daygones = VAR Day_ = IF( DAY(TODAY()) < 10 , "0" & FORMAT(DAY(TODAY()),"") , FORMAT(DAY(TODAY()),""))
VAR Month_ = IF( MONTH(TODAY()) < 10 , "0" & FORMAT(MONTH(TODAY()),"") , FORMAT(MONTH(TODAY()),""))
VAR Year_ = FORMAT(YEAR(TODAY()),"")
Return
Calculate ( MAX ( FAC_Workdays[ATLDF_] ) - 1 ,
'Date'[DATE DE] = Day_ & "." & Month_ & "." & Year_
)
And here is the result

However, I would like insteads of days gone shows  -1 (because I filtered month is September, and today is 1st October), days gone should back to the total number of working days when the day is the last day of month, is 22.

How could I do it with my calculation?

Thu

1 ACCEPTED SOLUTION
Community Support

Hi @ThuJa23

You could simplify your calculation by using Today_ = FORMAT(TODAY(),"dd.mm.yyyy") to get today's date in format "dd.mm.yyyy".

For daygones calculation, you could try below code. You will get yesterday's date by using today()-1.

``````daygones =
VAR Yesterday_ = FORMAT ( TODAY () - 1, "dd.mm.yyyy" )
RETURN
CALCULATE ( MAX ( FAC_Workdays[ATLDF_] ), 'Date'[DATE DE] = Yesterday_ )
``````

Hope this helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

4 REPLIES 4
Community Support

Hi @ThuJa23

You could simplify your calculation by using Today_ = FORMAT(TODAY(),"dd.mm.yyyy") to get today's date in format "dd.mm.yyyy".

For daygones calculation, you could try below code. You will get yesterday's date by using today()-1.

``````daygones =
VAR Yesterday_ = FORMAT ( TODAY () - 1, "dd.mm.yyyy" )
RETURN
CALCULATE ( MAX ( FAC_Workdays[ATLDF_] ), 'Date'[DATE DE] = Yesterday_ )
``````

Hope this helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi @v-jingzhang ,

Thanks so much, it works well 🙂

Super User

@ThuJa23 , create two measures like these and calculate percentage

Working days of month = countrows(filter(addcolumns(calendar(eomonoth(today(),-1)+1,eomonoth(today(),0)), "WorkDay", weekday([Date],2)),[WorkDay]>6))

Working days passed = countrows(filter(addcolumns(calendar(eomonoth(today(),-1)+1,today() ), "WorkDay", weekday([Date],2)),[WorkDay]>6))

Hi @amitchandak ,

It actually didn't work to me. As the result from below, with filter in September, Working day is just 5. It should be 22

Working day = countrows(filter(addcolumns(calendar(EOMONTH(today(),-1)+1,EOMONTH(today(),0)), "WorkDay", weekday([Date],2)),[WorkDay]>6))

Or am I wrong in anything?

Thanks

Thu

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors