How to get the number of days between 2 selected dates on a date slicer

[mchilders56]

I have a date slicer that is pulling from a Database. how do I give a number of days between these days. I need it to be able to put out 92 days for me or whatever the current selected date range is.  I have seen a lot of different things online but cannot seem to get anything to work. the closet I have gotten is the following but it gives me 28 days which is not right.

Days Between = DATEDIFF(

                    MIN(PS_TKT_HIST_LIN[BUS_DAT]),

                    MAX(PS_TKT_HIST_LIN[BUS_DAT]),

                    DAY

                    )

[Ritaf1983]

Hi @mchilders56 
Your formula works accurately, on which visual did you use it and got the wrong result?

pbix is attached

If my answer was helpful please give me a Kudos and accept as a Solution.

[mchilders56]

So I figure out how to get the actual number and I have it on my screen as a card. However here is where I run into problem is how to use that number. So I have the 92 days and want to use that specific number in a table that is listing out items that also have the last sale date on them. I am trying to show the Days on Hand of supply by doing

CALCULATE(SUM(IM_INV[QTY_ON_HND]) / (SUM(PS_TKT_HIST_LIN[QTY_SOLD])/VALUE([Days Between])))

so the value is not pulling the 92 days because of filters I think. so I need the value of Days beween to use the 92 days rather than the 28 days that it thinks from the last sale date of that item.

hopefully that makes sense.

[Ritaf1983]

Hi @mchilders56 

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[mchilders56]

so I have this line pulling from my DB based on the time frame in the slicer.  sales data for a particular item.

I have calculated the number of days between those dates and have outputed them and Max Date and Mindate using:

Days Between = DATEDIFF(

                    MIN(PS_TKT_HIST_LIN[BUS_DAT]),

                    MAX(PS_TKT_HIST_LIN[BUS_DAT]),

                    DAY

                    )

so to calculate the days on hand I would take QTY on Hand/ (QTY sold/Days between)

Days on Hand = (SUM(IM_INV[QTY_ON_HND]) / (SUM(PS_TKT_HIST_LIN[QTY_SOLD])/(CALCULATE(VALUE([Days Between]),  ALLEXCEPT(PS_TKT_HIST_LIN, PS_TKT_HIST_LIN[BUS_DAT])))))

but my numbers are not working right. I should be getting 61.33 but instead am getting 59.

if I change the date range it gets off even more. it's like it's doing something with the time frame from the maxdate in my slicer till the current date. but I don't know how to handle that part of it.

[Anonymous]

No offence, guys.

I think the ALLEXCEPT(PS_TKT_HIST_LIN, PS_TKT_HIST_LIN[BUS_DAT]) may effects the wrong reulst.

You could first test the result of below formula to see if it returns the correct days.

CALCULATE (VALUE ( [Days Between] ),ALLEXCEPT ( PS_TKT_HIST_LIN, PS_TKT_HIST_LIN[BUS_DAT] )

Since there is no detailed data to back it up, I can only suggest that you try the following modified measure to see if it can succeed.

Days on Hand =
(
    SUM ( IM_INV[QTY_ON_HND] )
        / (
            SUM ( PS_TKT_HIST_LIN[QTY_SOLD] )
                / (
                    CALCULATE (
                        VALUE ( [Days Between] ),
                        FILTER (
                            ALLSELECTED ( PS_TKT_HIST_LIN ),
                            [BUS_DAT] = MAX ( PS_TKT_HIST_LIN[BUS_DAT] )
                        )
                    )
                )
        )
)

Best Regards,

Stephen Tao

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

[marcelsmaglhaes]

Hey @mchilders56 

Instead of using DATEDIFF, you can directly calculate the difference in days by subtracting the minimum date from the maximum date selected and adding 1 day to include both start and end days.
Days Between =
IF(
ISBLANK(MIN(PS_TKT_HIST_LIN[BUS_DAT])) || ISBLANK(MAX(PS_TKT_HIST_LIN[BUS_DAT])),
BLANK(),
MAX(PS_TKT_HIST_LIN[BUS_DAT]) - MIN(PS_TKT_HIST_LIN[BUS_DAT]) + 1
)