Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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:      ...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.