topic Re: DateDiff until end of month in DAX Commands and Tips

DateDiff until end of month

Anonymous — Sun, 05 Feb 2023 14:31:00 GMT

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 ?

Re: DateDiff until end of month

Anonymous — Sun, 05 Feb 2023 14:49:27 GMT

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,

Re: DateDiff until end of month

Anonymous — Sun, 05 Feb 2023 17:19:52 GMT

Ok but what can I do with it after ?

Re: DateDiff until end of month

tamerj1 — Sun, 05 Feb 2023 18:14:02 GMT

Hi Anonymous

what is the dax of this measure?

Re: DateDiff until end of month

Anonymous — Mon, 06 Feb 2023 08:20:48 GMT

In other words ? I didn't understand

Re: DateDiff until end of month

tamerj1 — Mon, 06 Feb 2023 08:51:15 GMT

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?

Re: DateDiff until end of month

Anonymous — Tue, 07 Feb 2023 07:08:50 GMT

This is my measure :

lastchange=

var currentproduct = table[product]
var currdate = table[date]
var maxdate =

MINX(FILTER(Table,Table[Date]>currdate && Table[product]=currentproduct), table[date])

RETURN

(DATEDIFF(currdate,maxdate,SECOND)/3600)

Re: DateDiff until end of month

tamerj1 — Tue, 07 Feb 2023 08:12:07 GMT

Anonymous
Seems to me this is a calculated column rather than a measure?

Re: DateDiff until end of month

Anonymous — Tue, 07 Feb 2023 08:27:18 GMT

Yes you right

but even if it was a measure it not get in count the end of the month and continue to display cumulated values

Re: DateDiff until end of month

tamerj1 — Tue, 07 Feb 2023 08:42:03 GMT

Anonymous
If you have a sample file please share it's download link.

Re: DateDiff until end of month

Anonymous — Wed, 08 Feb 2023 14:23:29 GMT

I can't because this is confidential data...

but

this is like this (I filtered just to see one product) the result in hours.

For example in line 9, the product was in status and line 10 an other status. But meantime this is a new month and if want to display data for December month, it said that there are 264hours between the 27/12/2017 and 07/01/2018. But this is not true because part of the hours in January and not December.

Re: DateDiff until end of month

tamerj1 — Tue, 07 Feb 2023 11:45:09 GMT

Hi Anonymous
Never asked to share your data. Of course it is confidential. But you can allways create a sample PBIX that contains a data model that simulates your data. Inside import sample data of all invloved tables create the relationships and and create the visual that you are looking for without the required measure(s). This will save for both of us alot of time. Thank you.

Re: DateDiff until end of month

Anonymous — Tue, 07 Feb 2023 15:55:38 GMT

Infortunnaly I can't but i don't think we need this to write a Dax measure