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 completed the check against a list of shops.

I want to be able to show the percentage that have completed it each quarter on a rolling basis. I have a date table and so far ihave been able to create the below that shows the date of completion but how can i create a percentage count from this that I dont need to change each quarter? I think i need a measure but struggling to write it at the moment

thanks for any help

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!

5 Replies

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi

       

      that worked - thank you so much for your help

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Anonymous 

    Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind (for example for row1, what are your expected values and why?). You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

  • 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.