Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
obriaincian
Resolver I
Resolver I

Power BI - Get the number of days a date changes over the months

I have a dataset that has 3 columns Product, Expiry Date and Report Date.

 

Report Date           Product  Expiry Date
Dec-21PPPT-0125/01/2024
Dec-21WEC-5405/06/2023
Dec-21ERRE-6623/08/2025
Jan-22PPPT-0101/01/2024
Jan-22WEC-5405/06/2023
Jan-22ERRE-6623/08/2025
Feb-22PPPT-0101/01/2024
Feb-22WEC-5415/06/2023
Feb-22ERRE-6623/08/2025
Mar-22PPPT-0125/01/2024
Mar-22WEC-5415/06/2023
Mar-22ERRE-6623/08/2025

 

What I a trying to do is create a 4th column called difference. This will output the number of days (+/-) the expiry date has changed per product from the previous month.

The current forumula I'm using is this but it's not working, any suggestions would be appreciated.

 

difference=
sum(Data[Expiry Date])-CALCULATE(SUM(Data[Expiry Date]),PREVIOUSMONTH(Data[Expiry Date].[Date]))

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@obriaincian , Make sure report_date is date , a new column

 

new column =
var _date = eomonth([report_date,-1)
var _max = maxx(filter(Table, [Product] =earlier([Product]) && eomonth([report Date],0) =_date ), [Expiry Date] )
return
datediff(_max,[Expiry Date],day)

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@obriaincian , Make sure report_date is date , a new column

 

new column =
var _date = eomonth([report_date,-1)
var _max = maxx(filter(Table, [Product] =earlier([Product]) && eomonth([report Date],0) =_date ), [Expiry Date] )
return
datediff(_max,[Expiry Date],day)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors