% of month gone

[Anonymous]

Hi everyone,

In one of my MTD reports, I have a card with % of month gone excl. Saturday and Sunday. To do that I have created three measures; Workdays worked, Workdays in month and % of month gone. It works fine but the problem appears when I filter on the slicer, then it shows "(Blank)". The measures will be detailed below:

Workdays worked = 

CALCULATE(COUNT('Invoked Function'[WorkingDay]);'Invoked Function'[WorkingDay]="True";'Invoked Function'[MonthOfYear]=MONTH(TODAY());'Invoked Function'[Year]=YEAR(TODAY());'Invoked Function'[Date]<NOW()-1)

Workdays in month = 

CALCULATE(COUNTROWS('Invoked Function');'Invoked Function'[WorkingDay]="True";'Invoked Function'[MonthOfYear]=MONTH(TODAY());'Invoked Function'[Year]=YEAR(TODAY()))

% of month gone = [Workdays worked]/[Workdays in month]

Is there a better or more efficient way to do this? And how can I avoid the "(Blank)"?

Thank you 🙂

[Sabeeluzzama]

You can use MTD. But that will work, but when there is the date selected. Also, use it on the calendar, not on the table

Passed days = CALCULATE(count('Date'[Working day]),DATESMTD('Date'[Date]))

Month days = CALCULATE(count('Date'[Working day]),DATESMTD(endofmonth('Date'[Date])))

[amitchandak]

You can use MTD. But that will work, bets when there is the date selected. Also, use it on the calendar, not on the table

Passed days = CALCULATE(count('Date'[Working day]),DATESMTD('Date'[Date]))

Month days = CALCULATE(count('Date'[Working day]),DATESMTD(endofmonth('Date'[Date])))

Check calendar setting in file : https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

Hi @amitchandak,

The report doesn't have any date slicer. It is static but it shouldn't show (Blank) when I, for example, choose a specific team through the slicer.

[amitchandak]

That will filter, if you use datemtd or totalmtd, along with calendar table.

So either select a date from slicer , or create a calendar till today

date = Calendar(date(2017,01,01),today())

[Anonymous]

@amitchandak When I use count then it counts every day in the month, it should only count the workdays. This means it should return 18 in terms of passed days

[amitchandak]

Yes, you are right. Work day should be sum, because it has 1 and 0 values. If it does not work. Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.

[Anonymous · Accepted solution]

Hi @amitchandak , I edited the interactions of the slicer. That worked 🙂

[Anonymous]

You can create a calculated column:

MonthProgress% =
var MonthDays = DAY(
IF(MONTH(Table[date]) = 12,
DATE(YEAR(Table[date]) + 1,1,1),
DATE(YEAR(Table[date]), MONTH(Table[date]) + 1, 1)
) - 1)

return Table[Date].[Day] / MonthDays *100

[Anonymous]

Hi @Anonymous,

This wouldn't work next month? % of month gone should start from zero again automatically next month