Forum Discussion
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
- AnonymousNot 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,
- AnonymousNot applicable
Ok but what can I do with it after ?
- tamerj1Community Champion
Hi Anonymous
what is the dax of this measure?
- AnonymousNot applicable
In other words ? I didn't understand
- tamerj1Community 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?