Working days and Days gone

[ThuJa23]

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?

Thanks for your helps!

Thu

[v-jingzhang · Accepted solution]

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.

[ThuJa23]

Hi @v-jingzhang ,

Thanks so much, it works well 🙂

[amitchandak]

@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))

[ThuJa23]

Hi @amitchandak ,

Thanks for your replies. 

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