A Percentage Measure using just Fiscal date data

[SteCra100]

Hello folks. First of all, I just want to say how helpful the PowerBI Community has. They have been able to help me on so many occasions.  I can’t thank you enough!

I have a query, which I hope someone will be able to answer…

We have a fiscal year, which runs from 01 April 2020 to 31 March 2021. I have two columns:

Task Completed/Incompleted on Time

Date

The “Task Completed/Incompleted on Time” column just displays either 1 or zero. If the result set is 1, then the “Task” is completed on time.

Zero means the Task has been completed, but completed outwith the time set.

The “Date” column is just a date of when the task was completed.

I want to be able to create a percentage measure that displays any results that are 1. I have created a measure like below:

% YES =

DIVIDE (

    CALCULATE ( COUNT ( TableName[Tasks Completed/Incompleted on Time] ),

TableName[Tasks Completed/Incompleted on Time = "1" ),

    CALCULATE ( COUNT TableName[Tasks Completed/Incompleted on Time] ), ALLSELECTED ( TableName[Tasks Completed/Incompleted on Time)] ) )

)

The above is formatted to Percentage. But the above calculates all the Tasks that are equal to “1”, I just want to calculate Tasks that are equal to “1”, within the date range of 01 April 2020 to 31 March 2021.

Is there a way to do this?

Many thanks,

[amitchandak]

@SteCra100 , Try a measure like

% YES = divide(CALCULATE( COUNT ( TableName[Tasks Completed/Incompleted on Time] ),DATESYTD('Date'[Date],"3/31"),TableName[Tasks Completed/Incompleted on Time = "1") ,CALCULATE( COUNT ( TableName[Tasks Completed/Incompleted on Time] ),,DATESYTD('Date'[Date],"3/31")))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[SteCra100]

Hello @amitchandak 

Many thanks for your quick reply and thank you for your measure syntax. I have used the following syntax for my measure:

%Completed on Time = divide(CALCULATE( COUNT ( InsertTableNameHere[Tasks Completed/Incompleted on Time] ),

DATESYTD('InsertColumnNameHere'.[Date],"31/03/2031 23:59:00"),

InsertTableNameHere[Tasks Completed/Incompleted on Time = "1") ,

CALCULATE( COUNT ( InsertTableNameHere[Tasks Completed/Incompleted on Time] ),,

DATESYTD('InsertColumnNameHere'.[Date],"31/03/2031 23:59:00")))

However, I get a error:

"Argument '2' in CALCULATE function is required."

I'm not quite sure what this error means? Does this mean I need another argument in the syntax above?

Many thanks,

[V-pazhen-msft · Accepted solution]

@SteCra100 
You had double commas in the 2nd Calculate function. You may also try use / instead of divide() for easy reading. Try:

%Completed on Time = 
CALCULATE( COUNT ( InsertTableNameHere[Tasks Completed/Incompleted on Time] ),DATESYTD('InsertColumnNameHere'.[Date],"31/03/2031 23:59:00"),Filter(InsertTableNameHere,
InsertTableNameHere[Tasks Completed/Incompleted on Time = "1") ) 
/
CALCULATE( COUNT ( InsertTableNameHere[Tasks Completed/Incompleted on Time] ),
DATESYTD('InsertColumnNameHere'.[Date],"31/03/2031 23:59:00")))

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

[SteCra100]

Hello @V-pazhen-msft 

I tweaked the measure slightly to work for me, but I got the result I desire for. Thank you!

Many thanks,