How to calculate Workdays Left in Month till today

[mdaamirkhan]

Hi All

I need help on dax How to calculate Workdays Left in Month till today (everyday Workdays Left in Month will be less )as I have attached the excel file as per that date I need to calculate the Workdays Left. For example March 2021 starts from 26th Feb and ends at 25th March this how month has given as per that need to calculate.

If you have can provide me the dax and pbix file that will be very helpful.

excel file: https://drive.google.com/file/d/1nopUPOFtbuLyy0PeCdK8HJaoz08Gxl4A/view?usp=sharing

[Anonymous · Accepted solution]

@mdaamirkhan 

Remove the blanks rows first, the create following 3 calculated columns. You should see the pbix file in my previous post.

monthendday = CALCULATE(MIN([DATE BL]),FILTER('Date',[DATE BL]>=EARLIER([DATE BL]) && [DATE BL].[Day]=25))
Weekday = IF(WEEKDAY([DATE BL])<=6,1)
Result = IF([Weekday]=1, CALCULATE(SUM('Date'[Weekday]),ALLEXCEPT('Date','Date'[monthendday]))
- CALCULATE(SUM([Weekday]),FILTER(ALLEXCEPT('Date','Date'[monthendday]),[DATE BL]<=EARLIER([DATE BL]))),0)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[mdaamirkhan]

Hi All

I need help on dax How to calculate Workdays Left in Month till today it should be in Days (everyday Workdays Left in Month will be less )as I have attached the excel file as per that date I need to calculate the Workdays Left. For example each month starts from 26th Feb and ends at 25th March this will be March next month will start from 26 March till 25th April it will be April this how month has given as per that need to calculate.

If you have can provide me the dax and pbix file that will be very helpful.

excel file: https://drive.google.com/file/d/1nopUPOFtbuLyy0PeCdK8HJaoz08Gxl4A/view?usp=sharing

[v-xulin-mstf]

Hi @mdaamirkhan,

Try measure as:

Measure = 
var weekend1=
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        WEEKDAY('Table'[DATE BL])>=6 && WEEKDAY('Table'[DATE BL])<=7 && 'Table'[DATE BL]>=TODAY() && 'Table'[DATE BL]<=DATE(YEAR(TODAY()),MONTH(TODAY()),26)
    )
)
var weekend2=
CALCULATE(
 COUNT('Table'[DATE BL]),
    FILTER(
        ALL('Table'),
        WEEKDAY('Table'[DATE BL])>=6 && WEEKDAY('Table'[DATE BL])<=7 && 'Table'[DATE BL]>=TODAY() && 'Table'[DATE BL]<=EOMONTH(TODAY(),0)
    )
)
var weekend3=
CALCULATE(
 COUNT('Table'[DATE BL]),
    FILTER(
        ALL('Table'),
        WEEKDAY('Table'[DATE BL])>=6 && WEEKDAY('Table'[DATE BL])<=7 && 'Table'[DATE BL]>EOMONTH(TODAY(),0) && 'Table'[DATE BL]<DATE(YEAR(TODAY()),MONTH(TODAY())+1,26)
    )
)
return 
IF(
    DAY(TODAY())<26,
    26-DAY(TODAY())-weekend1,
    DAY(EOMONTH(TODAY(),0))-DAY(TODAY())+25-weekend2-weekend3
)

Here is the output:

The pbix file is attached.

Best Regards,

Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[mdaamirkhan]

same value is showing 18 if any month I select. It showld change. And f you see date March start from 26th Feb to 25th March . Again Next Month April will start from 26th March till 25th April

[v-xulin-mstf]

Hi @mdaamirkhan，

Sorry, I didn't figure out your needs before.
The measure returns the remaining working days of the current month to today
Do you want this result and a summary of the previous month's working days?
Or do you want to select any date and return the remaining days of the month in which that date is located?
In this case you need to create a date dimension table and replace 'Today()' in the measure.

Best Regards,

Link

[mdaamirkhan]

ok And days should be reduce by 1 everyday as per the today()

[v-xulin-mstf]

Hi @mdaamirkhan,

Ah yes, perhaps I was confused about the demarcation between the start date and the deadline.😅

Best Regards,

Link

[mdaamirkhan]

can you provide me the dax as per that. Days will be reduced everyday.

[mdaamirkhan]

ok can you provide me the dax as per that. Days will be reduced everyday.

[mdaamirkhan]

Will I get any solution from the group. Will someone who experience on dax to help on this

[mdaamirkhan]

 Does anyone in the community has knowledge or experience on this. Can you please provide me the solution on this 

As I already attached the excel file

[mdaamirkhan]

Only sunday will be removed. In the file actual date is like that. Everyday it will less days left in month.

For example each month starts from 26th Feb and ends at 25th March this will be March next month will start from 26 March till 25th April it will be April

[Anonymous]

Do you plan on removing bank holidays? or just weekdays? 

[mdaamirkhan]

Any update as I already replied you 

Only sunday will be removed. In the file actual date is like that. Everyday it will less days left in month.

For example each month starts from 26th Feb and ends at 25th March this will be March next month will start from 26 March till 25th April it will be April

[Anonymous]

I'm not fully following a few points. 
what do you mean by each month starts from "26th Feb and ends at 25th March". Will April start on the 26th of Feb also? I'm just very confused by this statement. 

Going from your date column, you want to know how many working days are left until today (26th March 2021, 27th March 2021 tomorrow and so on..) is that correct? 

You've provided a sample of the dates but you've not provided a sample of what your desired results would be. If you add that to your sample, it'll help us gauge a much clearer understanding of what you're trying to achieve. 

[mdaamirkhan]

Yes, I want to know how many working days are left until today (but month start from 26th Feb 2021, till 25th March 2021 that will be March.

hope you understand that

[mdaamirkhan]

any update 

[mdaamirkhan]

Will I get any solution from the group

[Anonymous]

@mdaamirkhan 
Then how do you distinguish workdays? Isn't Friday to Monday? or something else?

Paul

[mdaamirkhan]

Only sunday will be off . Means you can  remove sunday only. 
Then it should take From Monday to Saturday only