Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
@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.
@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
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.
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
Ignore the above %, got the 66.93% as you have shown, but need the FY and months to reflect on month filter
I got it, Thanks!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |