Forum Discussion

obriaincian's avatar
obriaincian
Icon for Resolver I rankResolver I
4 years ago
Solved

DATESINPERIOD not working

Hi All,

 

I have a Power BI Dashboard where I want to count the number of distinct dates in the Expiry column but I also want to filter the Date column to display rows that are within the last 3 months.

 

This is the code I am currently using:

 

last3Months = CALCULATE(distinctcount('Data'[Expiry]),'Data'[Expiry]<>BLANK(),DATESINPERIOD('Data'[Date],MAX('Data'[Date]),-3,MONTH))
 
The issue I'm having is it's not filtering the Date column for the last 3 months (Date column format is MMM-YY).
 
 
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi obriaincian 

     

    Try this measure

    last3Months 2 = 
CALCULATE (
    DISTINCTCOUNT ( 'Data2'[Expiry Date] ),
    'Data2'[Expiry Date] <> BLANK (),
    'Data2'[Date] > EOMONTH(MAX(Data2[Date]),-3)
)-1

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

9 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi obriaincian 

     

    Do you use this code for a measure? Which visual do you use it in? Based on my test, if it's a measure and placed in a card visual, it works correctly. 

     

    Perhaps you have put it in a visual with other fields that may pass other filters to influence the result. And ensure that the Date column should be of Date type. It doesn't matter which format it is. 

     

    If it still doesn't work, can you provide some sample data and current result/expected result to show the problem? 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • obriaincian's avatar
      obriaincian
      Icon for Resolver I rankResolver I
      4 years ago

      Hi v-jingzhang , this is a measure and it is used in a matrix visual (along with some other fields).

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi obriaincian 

         

        Do you want other fields to filter the result? If you don't want that, you can try adding ALLEXCEPT in the measure.

        last3Months =
CALCULATE (
    DISTINCTCOUNT ( 'Data'[Expiry] ),
    ALLEXCEPT ( 'Data', 'Data'[Date] ),
    'Data'[Expiry] <> BLANK (),
    DATESINPERIOD ( 'Data'[Date], MAX ( 'Data'[Date] ), -3, MONTH )
)

         

        Jing