Getting the from and till dates from a between two dates slicer

[Anonymous]

Hello,

I use a table F_DOSSIERS with the fields EMPLOYEE, REGION, OFFICE, CREATION_DATE, AMMOUNT_DOSSIERS:

I added a slicer to my report based on the field CREATION_DATE:

When I choose two dates between:

I want to see 4 dates depending on the two dates selected in the slicer:

Start Previous Year gives me the beginning of the month of the from value in the slicer minus a year: 1/5/2020 → 01/01/2019
End Previous Year gives me the end of the month of the till value in the slicer minus a year: 3/28/2020 → 31/3/2019

Start gives me the beginning of the month of the from value in the slicer: 1/5/2020 → 01/01/2020
End gives me the end of the month of the till value in the slicer: 3/28/2020 → 31/3/2020

Maybe I should do this with 4 measures, but then I need to get the two values from the slicer.

How can I do this?

Thanks,

R.W.

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create measures like DAX below.

Measure Min Date = CALCULATE(MIN (F_DOSSIERS[CREATION_DATE]), ALLSELECTED(F_DOSSIERS[CREATION_DATE]))



Measure Max Date = CALCULATE(MAX (F_DOSSIERS[CREATION_DATE]), ALLSELECTED(F_DOSSIERS[CREATION_DATE]))



Measure Start = EOMONTH([Measure Min Date], -1)+1



Measure End = EOMONTH([Measure Max Date], 0)



Measure Start Previous Year = EOMONTH([Measure Min Date], -13)+1



Measure End Previous Year = EOMONTH([Measure Max Date], -12)

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

@v-xicai 

Thanks,

This almost works perfectly.

Only I get 02/01/2020 (dd/mm/yyyy) for [Measure Start] when I set the from-slider on the slicer on 01/01/2020 (dd/mm/yyyy).

How ackward!
The [Measure Start Previous Year] is correct on 01/01/2019 (dd/mm/yyyy).

R.W.

[parry2k]

@Anonymous try following measures

Min Date = MIN ( Table[CreationDate] )
Max Date = MAX ( Table[CreationDate] )

start of year py = DATE(YEAR([Min Date])-1,1,1)

start of year ty = DATE(YEAR([Min Date]),1,1)

end of month ty = EOMONTH([Max Date],0)

end of month py = EOMONTH(DATE(YEAR([end of month])-1,month([end of month]),1),0)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

@parry2k 

Thanks
I could use the measures for Min Date en Max Date. The other measures were not exactly as needed, but I could correct them.

Measure Min Date = MIN (F_DOSSIERS[CREATION_DATE])

Measure Max Date = MAX (F_DOSSIERS[CREATION_DATE])

Measure Start = DATE(YEAR([Measure Min Date]); MONTH([Measure Min Date]); 1)

Measure End = EOMONTH([Measure Max Date];0)

Measure Start Previous Year = DATE(YEAR(MY_MEASURES[Measure Start]) - 1; MONTH(MY_MEASURES[Measure Start]); 1)

Measure End Previous Year = DATE(YEAR(MY_MEASURES[Measure End]) - 1; MONTH(MY_MEASURES[Measure End]); DAY(MY_MEASURES[Measure End]))

 

One will notice that [Measure Min Date] doesn't result in 30/11/2019 but in 02/12/2019. 
I tought that this was because the slicer gives all the dates between the minimum value and the maximum value in the CREATION_DATE in table F_DOSSIERS: 30/09/2019 en 05/05/2020 but in the following example it doesn't fit:

R.W. 

[parry2k]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

Once this data table is in the model, use slicer from here and all the calculations based on this date dimension.

I tested everything my end, and it worked fine. Share pbix file if you still run into an issue