Reply
Frequent Visitor
Posts: 7
Registered: ‎11-28-2018
Accepted Solution

Using datesbetween

Hello!

I Have the following problem, I have 3 columns: My initial Date, Final Date, and production between the dates, like the folowwing example:

Initial DateFinish DateProduction
01//01/201801//01/2019365


I want to make a measure that divide the production by the numbers of days and list that in my calendar table, with all the dates included.

Thanks in advance!



Accepted Solutions
Community Support Team
Posts: 1,402
Registered: ‎07-25-2018

Re: Using datesbetween

Hi @gertjvr

 

You may add a column for the sample table:

Days = DATEDIFF(Table[Initial Date],Table[Finish Date],DAY)

Then you may get the measure and show all the dates included.

Divide =
CALCULATE (
    DIVIDE ( MAX ( Table[Production] ), MAX ( Table[Days] ) ),
    FILTER (
        GENERATE ( 'Calendar', Table ),
        'Calendar'[Date] >= Table[Initial Date]
            && 'Calendar'[Date] <= Table[Finish Date]
    )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Community Support Team
Posts: 1,402
Registered: ‎07-25-2018

Re: Using datesbetween

Hi @gertjvr

 

You may add a column for the sample table:

Days = DATEDIFF(Table[Initial Date],Table[Finish Date],DAY)

Then you may get the measure and show all the dates included.

Divide =
CALCULATE (
    DIVIDE ( MAX ( Table[Production] ), MAX ( Table[Days] ) ),
    FILTER (
        GENERATE ( 'Calendar', Table ),
        'Calendar'[Date] >= Table[Initial Date]
            && 'Calendar'[Date] <= Table[Finish Date]
    )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Posts: 1,402
Registered: ‎07-25-2018

Re: Using datesbetween

Hi @gertjvr

 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

 

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 7
Registered: ‎11-28-2018

Re: Using datesbetween

It worked!
The only thing I changed was instead of using MAX, i Used SUM.
Highlighted
Frequent Visitor
Posts: 7
Registered: ‎11-28-2018

Re: Using datesbetween

Hello!

I discovered a little issue that caused a big problem for me...

Basically the given answers worked really fine if you have only date type, but my data presents DATE and TIME information, so it should be a little like this...

 

AttributteInitial Date- timeFinish Date - timeProduction
A01//01/2018 00:0001//03/2019 12:0060
B01/03/2018 12:0001/04/2018 23:5936



So, if you follow the DAX formula on the column =

Days = DATEDIFF(Table[Initial Date],Table[Finish Date],DAY)



and the following measure =
 Divide =
CALCULATE (
    DIVIDE ( SUM ( Table[Production] ), SUM ( Table[Days] ) ),
    FILTER (
        GENERATE ( 'Calendar', Table ),
        'Calendar'[Date] >= Table[Initial Date]
            && 'Calendar'[Date] <= Table[Finish Date]
    )
)

The formula works well if you dont show it by atributte, but when I start adding atributte in any visual it gives me the "wrong answer".

What happens is that if I put that in a graph, it will show to me that between day 1 and 3, it produced and average of 20 of atributte A , but that is not true, because it stopped in the middle of the day (it should be 24 per day).

And the same will happen with the Atributte B, it wil show and average of 18 between day 3 and 4 even that started only in the middle of day 3(should be 24 also).

What will happen is that in the "transaction" day (day 3), it shows a production of 20 products A and 18 of product B, given me a total of 38, and not 24 has was suppose to be.

Basically I need to find a way of correcting the number bat also make it link with my calendar table....

The ideia is to have that number in the most acurrate way, in the minutes scale if possible.

Thanks in advance,