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
Anonymous
Not applicable

How do I calculate days gone for dynamic months in Power BI

How do I calculate days gone for dynamic months in Power BI?

 

Example : JUL - 100%, AUG- 100%, current month is Sep - formula should be Days gone/no of working days in a month

 

Days gone should minus holidays too ( example : minus 2 fridays since I am in Middle East)

 

I have my calendar date,month and working days along with Month number. 

 

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

First create 2 columns as below:

Is workingday = IF(WEEKDAY('Table'[Date])=5,0,1)
Month = MONTH('Table'[Date])

Then create a measure as below;

Time gone = IF(SELECTEDVALUE('Table'[Month])<MONTH(TODAY()),"100%",IF(SELECTEDVALUE('Table'[Month])>MONTH(TODAY()),"0%",IF(SELECTEDVALUE('Table'[Month])=MONTH(TODAY()),
var a=DIVIDE(CALCULATE(SUM('Table'[Is workingday]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[Date]<TODAY()&&'Table'[Is workingday]=1)),CALCULATE(SUM('Table'[Is workingday]),FILTER(ALL('Table'),'Table'[Month]=MONTH(TODAY()))))
Return
FORMAT(a,"percent"))))

And you will see:

Screenshot 2020-09-22 141621.pngScreenshot 2020-09-22 141631.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?

 

Maybe you need something like Net Work Days? https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362


Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg- Thanks for your quick reply.

 

Below is a sales dashboard screenshot for my filters, for every salesperson we have a formula for Time gone (Days complete). The formula is (Days gone)/No of working days(26)

 

Amri25_0-1600587077944.png

Today is the 20th Sep, so ill take sales of the prior day , 19th Sep,and then subtract 3 fridays (non working days)from it.

 

Total =19-3=16 days

Days gone=16

No of working days = 26

Time Gone = 16/26=61.54% for Sep

 

Currently, i put a manual calc for Days gone as 16(below dax). No of workdays is the number of working days(26) as shown in dates table 

Amri25_1-1600587197203.png

Below is my dates table- working days highlighted, all days except Fridays are 1 and Fridays are 0.

Amri25_2-1600587355862.png

DAX measure for number of workdays:

Amri25_3-1600587460755.png

 

The formula should be dynamic for every month , example Jul & Aug is complete so it should be 100% instead of 61.54%.

 

Thanks

Hi @Anonymous ,

 

First create 2 columns as below:

Is workingday = IF(WEEKDAY('Table'[Date])=5,0,1)
Month = MONTH('Table'[Date])

Then create a measure as below;

Time gone = IF(SELECTEDVALUE('Table'[Month])<MONTH(TODAY()),"100%",IF(SELECTEDVALUE('Table'[Month])>MONTH(TODAY()),"0%",IF(SELECTEDVALUE('Table'[Month])=MONTH(TODAY()),
var a=DIVIDE(CALCULATE(SUM('Table'[Is workingday]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[Date]<TODAY()&&'Table'[Is workingday]=1)),CALCULATE(SUM('Table'[Is workingday]),FILTER(ALL('Table'),'Table'[Month]=MONTH(TODAY()))))
Return
FORMAT(a,"percent"))))

And you will see:

Screenshot 2020-09-22 141621.pngScreenshot 2020-09-22 141631.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

Thank you Kelly, you're a genius!

 

How do I put these months in my financial year Jul-Jun instead of Jan-Dec. I tried added month name in the filter for month box but it didnt come.

 

It should be Jul-1, Aug -2, Sep-3....etc

 

Amri25_0-1600761080101.png

 

 

 

Anonymous
Not applicable

Ignore the above %, got the 66.93% as you have shown, but need the FY and months to reflect on month filter

 

Amri25_0-1600762046507.png

 

Anonymous
Not applicable

I got it, Thanks!

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.