Weekly Progress Percentage in Line Chart-DirectQuery

[LAIKSAYYED3]

I am connected to a SQL Table in DirectQuery mode and I want to create a Line Chart visualizing the Total Weekly Compliant Percentage achieved by the end of the week ....

Formula for Compliant Percentage = Number of Documents Uploaded Minus Number of Expired Documents * 100 DIVIDE BY NUMBER OF REQUIRED DOCUMENTS

Field in the Table are as ff:

DocumentID

DocumentType

DateUploaded  (if this field is blank, that means Document has not been uploaded)
ExpiryDate

IsRequired (True/False)

I created a separate DateTable, Added Column 'WeekNumber' and created following measures:

DocRequired = CALCULATE(COUNTA(TableName[IsRequired])) (THIS VALUE IS CONSTANT FOR ALL, IN MY CASE IT IS 2,189 DOCUMENTS REQUIRED)

ValidDocuments = CALCULATE(COUNTAX(TableName, TableName[ExpiryDate] > TableName[DateUploaded]))

Expired = CALCULATE(COUNTA(TableName[ExpiryDate]), TableName[ExpiryDate] < TODAY())

I am able to develop a Weekly progress line chart, as attached, however, it doesn't seems to be perfect, as there are few weeks, when the number of expired documents are more or less documents being uploaded, in that case the graph should show a downward trend in particular week, which is not happening ... 😞

Can anyone help me with a work around please...

Thanks in advance.

Laik 

[Greg_Deckler]

@LAIKSAYYED3 Sorry, having trouble following, can you post sample data as text and expected output?
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.