cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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-21 PPPT-01 25/01/2024 Dec-21 WEC-54 05/06/2023 Dec-21 ERRE-66 23/08/2025 Jan-22 PPPT-01 01/01/2024 Jan-22 WEC-54 05/06/2023 Jan-22 ERRE-66 23/08/2025 Feb-22 PPPT-01 01/01/2024 Feb-22 WEC-54 15/06/2023 Feb-22 ERRE-66 23/08/2025 Mar-22 PPPT-01 25/01/2024 Mar-22 WEC-54 15/06/2023 Mar-22 ERRE-66 23/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
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)

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)

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors