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.

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

Hi obriaincian 

 

Sorry I don't understand the expected result. Based on your sample data, if you want to count the distinct count of expiry dates for every product in the last three months, the results should all be 2. If you want to count how many times expiry dates change for every product in the last three months, both 7Up and Fanta should be 1. Can you explain how to get the expected output based on the sample data?

 

Regards,

Jing

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.