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

[Anonymous]

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. 

[Greg_Deckler]

@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.

[Anonymous]

@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)

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 

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

DAX measure for number of workdays:

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

Thanks

[v-kelly-msft · 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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

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

[Anonymous]

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

[Anonymous]

I got it, Thanks!