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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply

Calculating date and count columns?

Hi all! 

 

Simple task that I cannot get right! 

 

Two columns, one denotes the date of a next product renewal (start_date) 

The other is a whole number representing the lifecycle of each product (cycle_length) 

 

I want to create a new column which calculates a future date of the next renewall based on the start date plus the cycle length. 

 

Each product has a different cycle length so needs to read the cycle lenght column for each row. 

 

Example start date is 01/07/2023, cycle legnth is 20 years, next renewal colunn should read 1/07/2043 (simplied for example) 

 

Any help much appreciated 🙂 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Googlecanthelp ,

It seems that there is null value in the field 'rm_loc_component_cycles'[next_date], and in order to avoid February having different days in different years get error, for example next_date is 2024/2/29, cycle length is 3. Then the next renewal will be date(2027,2,29) base on your formula. It will also be wrong....

vyiruanmsft_0-1690868936654.png

So please update the formula of calculated column [Next renewal] as below and check if it can return the expected result... Please find the details in the attachment.

Next renewal = 
VAR _year =
    YEAR ( 'rm_loc_component_cycles'[next_date] ) + 'rm_loc_component_cycles'[cycle_length]
VAR _month =
    MONTH ( 'rm_loc_component_cycles'[next_date] )
VAR _day =
    DAY ( 'rm_loc_component_cycles'[next_date] )
VAR _eomonth =
    EOMONTH ( DATE ( _year, _month, 1 ), 0 )
RETURN
     IF (
         ISBLANK ( 'rm_loc_component_cycles'[next_date] ),
         BLANK (),
        IF ( _day > DAY ( _eomonth ), _eomonth, DATE ( _year, _month, _day ) )
     )

Best Regards

View solution in original post

7 REPLIES 7
punitkhatri
Helper III
Helper III

Hii @Googlecanthelp , You can also use the DATEADD function
It will return the dates according to the interval you choose. In your case the formula would be :-
Formula = DATEADD(Start_date, cycle_Length, Year)
You can also refer this link for more information.
https://learn.microsoft.com/en-us/dax/dateadd-function-dax

Next renewal = SUMX(rm_loc_component_cycles,DATE(YEAR('rm_loc_component_cycles'[next_date].[Date])+'rm_loc_component_cycles'[cycle_length],MONTH('rm_loc_component_cycles'[next_date].[Date]),DAY('rm_loc_component_cycles'[next_date].[Date])))
 
Error showing as 
 
Googlecanthelp_0-1689595494826.png

 

Anonymous
Not applicable

Hi @Googlecanthelp ,

It seems that there is null value in the field 'rm_loc_component_cycles'[next_date], and in order to avoid February having different days in different years get error, for example next_date is 2024/2/29, cycle length is 3. Then the next renewal will be date(2027,2,29) base on your formula. It will also be wrong....

vyiruanmsft_0-1690868936654.png

So please update the formula of calculated column [Next renewal] as below and check if it can return the expected result... Please find the details in the attachment.

Next renewal = 
VAR _year =
    YEAR ( 'rm_loc_component_cycles'[next_date] ) + 'rm_loc_component_cycles'[cycle_length]
VAR _month =
    MONTH ( 'rm_loc_component_cycles'[next_date] )
VAR _day =
    DAY ( 'rm_loc_component_cycles'[next_date] )
VAR _eomonth =
    EOMONTH ( DATE ( _year, _month, 1 ), 0 )
RETURN
     IF (
         ISBLANK ( 'rm_loc_component_cycles'[next_date] ),
         BLANK (),
        IF ( _day > DAY ( _eomonth ), _eomonth, DATE ( _year, _month, _day ) )
     )

Best Regards

Next renewal = SUMX(rm_loc_component_cycles,DATE(YEAR('rm_loc_component_cycles'[next_date])+'rm_loc_component_cycles'[cycle_length],MONTH('rm_loc_component_cycles'[next_date]),DAY('rm_loc_component_cycles'[next_date])))

You don't need .[Date]. Check if this is the problem

mlsx4
Memorable Member
Memorable Member

Hi @Googlecanthelp 

 

I assume that all cycles' length are the same unit, so you can do:

Next renewal = SUMX('Table',DATE(YEAR('Table'[Date])+'Table'[Cycle length],MONTH('Table'[Date]),DAY('Table'[Date])))



Hi, thanks! The cycles units are years, so I want it to show me the next date plus the cycle years 

Then, use the formula I had written before. It should work

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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