Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago

datesinperiod

i am wondering why datesinperiod function is not working as expected

i am trying to calculate count of promoters for last 3 months

i used two methods to calculate the count , one without using datesinperiod () and one with using datesinperiod

i am getting correct results using without datesinperiod. please see below screenshots and pbix

pbix: 

https://drive.google.com/file/d/1rsfDOmgRYUvthgvbzVnKwbEYckKQP0ww/view?usp=drive_link

 

Promoters = COUNT(survey1[category])
promotersrolling =

var enddate=max(survey1[survey_date])
var startdate=EDATE(enddate,-3)
return
calculate(
    survey1[Promoters],
survey1[survey_date]>=startdate
&&
survey1[survey_date]<=enddate
)

 

 

promotersrolling1 =
CALCULATE(
    [Promoters],
    DATESINPERIOD(survey1[survey_date],max(survey1[survey_date]),-3,MONTH)
 
)
 

 

 

 
Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    powerbiexpert22 , In the first measure you need add +1 in start date

     

    promotersrolling =

     

    var enddate=max(survey1[survey_date])
    var startdate=EDATE(enddate,-3) +1 // end date +1
    return
    calculate(
        survey1[Promoters],
    survey1[survey_date]>=startdate
    &&
    survey1[survey_date]<=enddate
    )
     
    for all time intelligence functions use a date table joined with the date of your table. Use field from date table in visual, measure and slicer
     
    promotersrolling1 =
    CALCULATE(
        [Promoters],
        DATESINPERIOD(Date[Date],max(Date[Date]),-3,MONTH)
     
    )
     
    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.