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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jayPBI23
Helper I
Helper I

Calculate percentage based on two dates duration and today's date

I have two dates: 

 

policy start date: 1/1/2023   

policy end date:  1/20/2023

Duration: 20 days 

todays date: 1/10/2023 (this date will change daily)

 

For policy start and end dates 1/1/2023 - 1/20/2023 (20 Days). If looking ar report on 1/10/2023, the duration of the policy 1/1/2023 - 1/10/2023 (10 days) is at 50%. 

 

If looking at the report on 1/11/2023, the duration will be 1/1/2023 - 1/11/2023 (11 days), which will be 55%. 

 

So this number will change every day depending on when you look at the report. 

 

I want the duration column to be shown in percentage. How would I write this?

 

Thanks,

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @jayPBI23 ,

Please try below steps:

1. create a measure with below dax formula

Measure =
VAR start_date =
    DATE ( 2023, 1, 1 )
VAR end_date =
    DATE ( 2023, 1, 20 )
VAR cur_date =
    TODAY ()
VAR _diff1 =
    DATEDIFF ( start_date, end_date, DAY )
VAR _diff2 =
    DATEDIFF ( start_date, cur_date, DAY )
RETURN
    DIVIDE ( _diff2, _diff1 )

2. add a crad visual with this measure

vbinbinyumsft_0-1673403064290.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-binbinyu-msft
Community Support
Community Support

Hi @jayPBI23 ,

Please try below steps:

1. create a measure with below dax formula

Measure =
VAR start_date =
    DATE ( 2023, 1, 1 )
VAR end_date =
    DATE ( 2023, 1, 20 )
VAR cur_date =
    TODAY ()
VAR _diff1 =
    DATEDIFF ( start_date, end_date, DAY )
VAR _diff2 =
    DATEDIFF ( start_date, cur_date, DAY )
RETURN
    DIVIDE ( _diff2, _diff1 )

2. add a crad visual with this measure

vbinbinyumsft_0-1673403064290.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey thanks for the reply. What if  today's date (cur_date) is coming from a date filter. Is there a way to dynamically change that as the user chnages the date filter?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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