cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors