Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a calendar table where I'm using a calculated column to calculate the no. of working days in each month this year.
Based on this column and the current date, I want to calculate the no. of working days that have passed year to date. Please see screenshot below for no. of working days by month. Essentially, I want this formula to return 21 for January, 20 for Feb and 19 for March (since based on today's date we have 2 more working days left in March) and the total to be 60. I have tried a few different formulas but can't seem to get the desired result, mainly the total is almost always incorrect.
Thank you for any suggestions!
Solved! Go to Solution.
YTD Sales = CALCULATE(SUM('Date'[Working Day]),DATESYTD(('Date'[Date]),"12/31"))
But for that, you need have a filter of the date on the page
or try like
YTD Sales = CALCULATE(SUM('Date'[Working Day]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())
Hi @kartiklal18 ,
Take year 2020 for example:
1.Create a calendar table using below dax expression:
table = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))
2.Then create 3 calculated columns:
Month = FORMAT('table'[Date],"MMMM")
weekday = WEEKDAY('table'[Date],2)
Working day = IF('table'[weekday]<>6&&'table'[weekday]<>7,1,0)
3.Fianlly create a measure as below:
Measure = IF(MONTH(MAX('table'[Date]))=MONTH(TODAY()),CALCULATE(SUM('table'[Working day]),'table'[Date]<=TODAY()),SUM('table'[Working day]))
And you will see:
For the related .pbix file,pls click here.
First, 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
If you have a Calendar, then you should be able to create a column like:
Working Day = IF(WEEKDAY([Date],2)<6,1,0)
You should then just be able to SUM the Working Day column to get the number of working days and the totals should be correct.
If you are using some kind of measure, you may have a measure totals issue. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
YTD Sales = CALCULATE(SUM('Date'[Working Day]),DATESYTD(('Date'[Date]),"12/31"))
But for that, you need have a filter of the date on the page
or try like
YTD Sales = CALCULATE(SUM('Date'[Working Day]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |