Need help with days between dates in Slicer

[Himanshi0291]

Hi,

I am facing a difficulty in calculating total days between the dates selected in slicer for a scenario listed below - 

I have a Trans date column and interval for the same is selected in slicer and employees are required to post their working hours for each date in the month but for some reason, some always miss. 

Now, I want to calculate total days between the interval selected in slicer given above in table (column Trans date) irrespective of employee hours present or not. 

Problem - When I try to calulcate days between 15/06/2020 to 28/06/2020, my query gives me only 4 days i.e. days for which employee put the data but my need is to calculate total days between 15/06/2020 to 28/06/2020 (or any interval selected in slicer).  

Please guide how could that be achieved. 

[harshnathani]

Hi @Himanshi0291 ,

You can have a look at this video . See if this helps

https://www.youtube.com/watch?v=mm-Lv6lv4a4

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

Build your slicer from the Trans Date column and select any date range.  Write this measure

=MAX(Data[Trans date])-MIN(Data[Trans date])

Hope this helps.

[Himanshi0291]

Hi,

We tried using the expression suggested but it didnt work. 

Here is my expression -

Days = MAX(DK_Time_Temp[TransDate] - MIN(DK_Time_Temp[TransDate]))

It throws the error that MAX function only accepts a column reference as an argument.

But Transdate is already a column in table DK_TIME_TEMP. 

Please help. 

[V-lianl-msft]

Hi @Himanshi0291 ,

Please try measure like this:

Measure = 
var max_date = CALCULATE(MAX('Table'[Trans Date]),ALLSELECTED('Table'[Trans Date]))
var min_date = CALCULATE(MIN('Table'[Trans Date]),ALLSELECTED('Table'[Trans Date]))
return DATEDIFF(min_date,max_date,DAY)

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

[Himanshi0291]

Thanks for all your solutions & support.

With the help of MAX and MIN function, I am getting the maximum & minimum date seleted in slicer but it does not calculate working days between the two dates if there is no data available for some dates.

Here in the snapshot below, my slicer has min date of 15/06 and max date of 28/06 however count of my total working days is still 5 i.e. from 15/06 to 19/06 for which data is available in table. My need is to calculate total working days between 15/06 to 28/06 irrespective of the fact that data is available or not for those dates in table. 

What could be the best possible way to achieve the same? 

[Himanshi0291]

Hi Team,

Can anyone help me with this query?

Thank you in advance.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Ashish_Mathur]

Hi,

The MAX bracket should be closed before the - sign.

[Himanshi0291]

Sorry, the suggestion didn't help.

I tried:  Days = MAX(DK_Time_Temp[TransDate])-MIN(DK_Time_Temp[TransDate])

Outcome is weird: 10-01-1900 00:00:00

[harshnathani]

Hi @Himanshi0291 ,

See if this helps.

https://community.powerbi.com/t5/Desktop/Days-Between-Slicer-Dates-in-Measure/m-p/608387

https://community.powerbi.com/t5/Desktop/Measure-with-Date-from-Slicer/m-p/1144897

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[lbendlin]

for your date value in the visual enable "show items with no data"