Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DateDiff until end of month

Hi guys, 

I'm working on a table with several columns: date, product and status. Each product can have several statuses (working, not working). I'm looking to calculate how long each product stayed in a certain status. For this I have a measure that calculates for each product How long has it been until the next time it changes status and so on.

BUT I have a problem the result obtained does not reflect reality. 

if for example a product A was "working" from 01/01/2021 until 01/05/2021 (4 days difference) in this date it will "Not working". 
but the 02/05/2021 it will be "working" so my measure says me that passed from here 30 days. But actually that is not true. Because if I want to display for the January month how many days was the product "not working" so I'll get the result 30 ! But the reality is that in January the product was 25 days in this status and not 30, the five days have to be in the next month ! 

Actually dax have to understand that he have to stop the sum of days at the end of month if not I'lol get fake data with slicers. 
do you have a solution ? 

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    I think your table, you can add a calculated column, eomonth(date) and it should gove you the end of month of every respective row date, 

    • Anonymous's avatar
      Anonymous
      Not applicable

      Ok but what can I do with it after ? 

  • tamerj1's avatar
    tamerj1
    Community Champion

    Hi Anonymous 

    what is the dax of this measure?

    • Anonymous's avatar
      Anonymous
      Not applicable

      In other words ? I didn't understand 

      • tamerj1's avatar
        tamerj1
        Community Champion

        Anonymous 

        "For this I have a measure that calculates for each product How long has it been until the next time it changes status and so on."

        What DAX formula have you used for this measure?