locked
DAX - Calculate working days remaining in the current month RRS feed

  • Question

  • I'm trying to calculate the number of working days remaining in the current month.  My calendar table has the following fields:

    1. DATE_DESC
    2. MONTH_KEY
    3. WORKING_DAY_FLAG
    4. MTD_WORKING_DAY_FLAG
    5. CURRENT_MONTH_FLAG

    WORKING_DAY_FLAG is either 0 or 1 depending on whether the date is a working day.

    MTD_WORKING_DAY_FLAG is a calculated column with either a 0 or 1 defined by:

    =IF(AND(Calendar[DATE_DESC]<=MAX(BILLED[GL_DATE]),[WORKING_DAY_FLAG]=1),1,0)

    Basically, it will stop populating 1 for working days at the latest date found in the Billed table.

    CURRENT_MONTH_FLAG is a calculated column that places the value "CURRENT MONTH" in the column if the date falls in the current month.  It is defined as:

    =if(AND(format(max(Billed[GL_DATE]), "mmmm yyyy")=FORMAT([MONTH_KEY], "MMMM YYYY"), Calendar[DATE_DESC]<=MAX(Billed[GL_DATE])), "CURRENT MONTH", "")

    I use the CURRENT_MONTH_FLAG as a pivot table page filter so that my pivot table is always pulling in data for the current month.

    I have a measured called ELAPSED_WORKING_DAYS defined as:

    =sum([MTD_WORKING_DAY_FLAG])

    Another measured called WORKING_DAYS is defined as:

    =SUM([WORKING_DAY_FLAG])

    Using these fields, I can create a pivot table with MONTH_KEY in the row fields and ELAPSED_WORKING_DAYS and MONTHLY_WORKING_DAYS as values.  With the CURRENT_MONTH_FLAG set to (All), the table displays as you would expect, with the elapsed days in one column and total working days in the second column.  I can create a measures that takes the difference between these two and get the working days remaining.

    The problem arises when I use the CURRENT_MONTH_FLAG to filter by the current month.  Since CURRENT_MONTH_FLAG stops populating when the date in the Calendar table exceeds the last date in the Billed table, the WORKING_DAYS field only totals the WORKING_DAY_FLAG field up to that date. 

    Is there a way to create a DAX statement that will calculate the total working days for the month, regardless of the filter context set by the CURRENT_MONTH_FLAG?

    Sorry if this is confusing!

    Thanks!

    Tuesday, April 19, 2016 9:32 PM

Answers

  • Hi auaero,

    In this scenario, when you determine current month and working days flag, you always compare with the last day in Billed table. So when you filter date after the last day in Billed, it definitely will not get any CURRENT_MONTH_FLAG after that date. For your requirement, you should remove the "Calendar[DATE_DESC]<=MAX(Billed[GL_DATE])" condition in your CURRENT_MONTH_FLAG column.

    Regards,


    Simon Hou
    TechNet Community Support


    Saturday, April 23, 2016 5:48 AM

All replies

  • I don't quite understand how you determine current month, but you may want to take a look at the DAX Time Intelligence functions

    https://msdn.microsoft.com/en-us/library/ee634763.aspx

    ex:

    DATESMTD

    ENDOFMONTH

    Friday, April 22, 2016 12:09 AM
  • Hi auaero,

    In this scenario, when you determine current month and working days flag, you always compare with the last day in Billed table. So when you filter date after the last day in Billed, it definitely will not get any CURRENT_MONTH_FLAG after that date. For your requirement, you should remove the "Calendar[DATE_DESC]<=MAX(Billed[GL_DATE])" condition in your CURRENT_MONTH_FLAG column.

    Regards,


    Simon Hou
    TechNet Community Support


    Saturday, April 23, 2016 5:48 AM