Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Creating a percentage complete based on dates

Hi  I wonder if anyone can provide me with some help. I have a check that is completed once a quarter and the results of that check(completed via a form) are sent into PBI so we can show who has co...
Need Help
  • v-sdhruv's avatar
    v-sdhruv
    1 year ago

    Hi Anonymous ,

    Thank you for sharing the file and explaining in detail. Although I couldnt find completion Date in ShopList Table.
    You can try this measure in your file :

    Quarterly Completion % =
VAR TotalShops = CALCULATE(DISTINCTCOUNT('Shop List'[Shop Name]), ALL('Shop List'))
VAR CompletedShops = 
    CALCULATE(
        DISTINCTCOUNT('Shop List'[Shop Name]),
        FILTER(
            'Shop List',
            'Shop List'[Date Completed] >= MIN('Date Table'[Date]) &&
            'Shop List'[Date Completed] <= MAX('Date Table'[Date])
        )
    )
RETURN
DIVIDE(CompletedShops, TotalShops, 0)

    Let me know if it works for you.
    Thank You!

v-sdhruv's avatar
v-sdhruv
Community Support
1 year ago

Hi Anonymous ,

You can try something like this-

PercentageCompletedRollingQuarter =
VAR CurrentQuarterStart = STARTOFQUARTER('Date'[Date])
VAR CurrentQuarterEnd = ENDOFQUARTER('Date'[Date])
VAR TotalShops = CALCULATE(DISTINCTCOUNT('Shops'[ShopName]))
VAR CompletedShops = 
    CALCULATE(
        DISTINCTCOUNT('Table'[ShopNmae]),
        'Table'[CompletionDate] >= CurrentQuarterStart,
        'Table'[CompletionDate] <= CurrentQuarterEnd
    )
RETURN

Assuming you have Completion Date in a table and is connected to Shops and Date Table.
If I  isunderstood anything, kindly provide a sample data/ sample pbix file excluding sensitive data.

Thank You

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi

 

think this is sort of what I am trrying to do - thanks for the help.

I have some sample data in excel . https://www.dropbox.com/scl/fi/cs5jz5giy7d7rxi7a0byz/sample-data.xlsx?rlkey=e9misp6x2517qebf4gtjtn4jt&st=g1725hdd&dl=0 

I have a list of shops, a list of dates they completed the quarterley checks and a date table that shows quarters and financial years. 

 

What I am trying to do is to be able to show both a count of completion per quarter and FY and also a way to show an individual shop has completed per quarter. at the moment I have a calculated column which pulls the most recent date it was completed into the shop list but this relies on me changing the date each quarter.

SM Quarterly check  = IF('Shop List '[date last completed - SM quarterly check] > DATE(2025,06,29),"complete","incomplete")
 
Apologies if i have provided the data in an incorrect way.