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 mo...
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.

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

Hi v-jingzhang , Thank you for your response.

 

Apologies, I have now corrected the data and desired output.

 

Based on the sample data I'd like to filter the Date coulmn for all dates within the last 3 months (Jan, Feb, Mar) and get a distinct count of the Expiry date column (subtracted by 1, If there is only one expiry date that means no changes are made so I'd like to return zero here).

 

I then want to create a matrix with Products in column one and then the number of changes per product in the second column (similar to what I have in the desired outputs above)

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
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.