Forum Discussion

ctedesco3307's avatar
ctedesco3307
Icon for Resolver II rankResolver II
4 years ago
Solved

Date diff Working Days to Today with filter

I have a table that has all the working days identifed for a project between the start and end date. I need a measure to add up all the working days for each project from Start date to today, Thanks ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi ctedesco3307 ,

     

    I suggest you to try this code to create a measure.

    Measure = 
VAR _MINDATE = MIN(MAX('Table'[End Date]),TODAY())
VAR _COUNT = CALCULATE(SUM('Table'[Working Day]),FILTER('Table','Table'[MSSContractDay]<=_MINDATE))+0
RETURN
_COUNT

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

ctedesco3307's avatar
ctedesco3307
Icon for Resolver II rankResolver II
4 years ago

amitchandak  thank you but the function needs to sum the workingday column in the table if workingday = 1  -- not just omit saturday and sunday - we have company holidays also 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi ctedesco3307 ,

     

    I suggest you to try this code to create a measure.

    Measure = 
VAR _MINDATE = MIN(MAX('Table'[End Date]),TODAY())
VAR _COUNT = CALCULATE(SUM('Table'[Working Day]),FILTER('Table','Table'[MSSContractDay]<=_MINDATE))+0
RETURN
_COUNT

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      hi Rico,

      am using your DAX statement like this:

      DaysDiff =
       VAR _MINDATE= MIN(MAX('KPI Delivery'[InvoiceDate]), TODAY())
       VAR _COUNT = CALCULATE(SUM('KPI Delivery'[InvoiceWeekendDay]),FILTER('KPI Delivery', 'KPI Delivery'[InvoiceDate]<=_MINDATE))+0
       
       RETURN
       _COUNT
      The measure seems to be working but as soon as i add the measure to a visual i get this error message: 
      MdxScript(Model (5,29) Calculation error in measure TABLE [DaysDiff]; the function SUM cannot work with values of type string.  Can you help. thnx!!


      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        hi Rico,

        forget about my question, found the issue and solved it.

        The measure is working!

        thnx!!!!