Forum Discussion

ThuJa23's avatar
ThuJa23
Icon for Advocate II rankAdvocate II
4 years ago
Solved

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?

 

Thanks for your helps!

Thu

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      ThuJa23
      Icon for Advocate II rankAdvocate II
      4 years ago

      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

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.