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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Giada_Togliatti
Post Patron
Post Patron

percent of progress

Hi,

I have a date field called start date, I have a date field called end date, which formula can I use in dax to calculate the % of progress based on today date?

For example I started an activity on 1st of may, today is the 3rd may, the end date is 4of may and today I'm at 75% of progress.

Thank you

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @Giada_Togliatti 

I tried to create a sample pbix file based on your explanation.

Please check the below picture and the sample pbix file's link down below, whether it suits your case.

Or, share your sample pbix file's link here, then I can try to look into it to come up with a more relevant measure.

 

 

Picture1.png

 

Activity Progress =
VAR activitystart =
MAX ( Activities[StartDate] )
VAR activityend =
MAX ( Activities[Enddate] )
VAR selecteddate =
MAX ( Dates[Date] )
VAR activitytotaldays = activityend - activitystart + 1
RETURN
SWITCH (
TRUE (),
activityend <= selecteddate, 1,
activitystart <= selecteddate
&& activityend > selecteddate,
( selecteddate - activitystart + 1 ) / activitytotaldays,
activitystart > selecteddate, 0
)

 

 

https://www.dropbox.com/s/m5nceut2vqfqimp/giada%20togliatti.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
Super User

Hi, @Giada_Togliatti 

I tried to create a sample pbix file based on your explanation.

Please check the below picture and the sample pbix file's link down below, whether it suits your case.

Or, share your sample pbix file's link here, then I can try to look into it to come up with a more relevant measure.

 

 

Picture1.png

 

Activity Progress =
VAR activitystart =
MAX ( Activities[StartDate] )
VAR activityend =
MAX ( Activities[Enddate] )
VAR selecteddate =
MAX ( Dates[Date] )
VAR activitytotaldays = activityend - activitystart + 1
RETURN
SWITCH (
TRUE (),
activityend <= selecteddate, 1,
activitystart <= selecteddate
&& activityend > selecteddate,
( selecteddate - activitystart + 1 ) / activitytotaldays,
activitystart > selecteddate, 0
)

 

 

https://www.dropbox.com/s/m5nceut2vqfqimp/giada%20togliatti.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim , thank you for the reply, it works.

I want to create a filter too that shows the activity that are finished today (100%) and activity that are not finished, how can I do?

Hi, @Giada_Togliatti 

Thank you for your feedback.

I am not sure if I understood your question correctly.

Please check the below picture and the link.

Or, please let me have an idea about how your desirable slicer looks like. Then I can try to create it.

 

Activity Progress on Today =
VAR activitystart =
MAX ( Activities[StartDate] )
VAR activityend =
MAX ( Activities[Enddate] )
VAR selecteddate =
TODAY()
VAR activitytotaldays = activityend - activitystart + 1
RETURN
SWITCH (
TRUE (),
activityend <= selecteddate, 1,
activitystart <= selecteddate
&& activityend > selecteddate,
( selecteddate - activitystart + 1 ) / activitytotaldays,
activitystart > selecteddate, 0
)
 
 Picture1.png
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim , thank you for the reply, the measure is correct, but what I need is a slicer where I can choose between completed activity ( where the measure is 100%) or not completed activity, I need to be able to choose between the 2 option, something like this image but with the 2 option completed and not completed instead of activity.

Immagine.png

Hi, @Giada_Togliatti 

Please check the below picture and the measure that I used for the below visualization.

I created a new table (Slicer Table, disconnected table) in order to create a slicer. 

 

Picture1.png

 

Activity Progress on Today by group =
IF (
ISFILTERED ( Activities[Activity] ),
SUMX (
FILTER (
VALUES ( Activities[Activity] ),
COUNTROWS (
FILTER (
Slicer,
[Activity Progress on Today] >= Slicer[Min]
&& [Activity Progress on Today] < Slicer[Max]
)
) = 1
),
[Activity Progress on Today]
)
)

 

 

https://www.dropbox.com/s/m5nceut2vqfqimp/giada%20togliatti.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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