Formulas

[KhrystinaM]

Hello, 

I have a dashboard with SharePoint list entries. If items are not completed by a certain time they are marked as "Not Posted" I created a measure [Not Posted = CALCULATE(COUNTROWS('Status Board'),'Status Board'[Posted]="Not Posted")]. The department leads have requested that the times not posted only calculate for the date, so instead of times not posted = 7, it would =1 for that department on that date. I'm not sure how to write a formula for this? Hopeing someone can help.

Thank you!!

[v-kongfanf-msft]

Hi @KhrystinaM ,

Please provide test data and formulas created.

Best Regards,
Adamk Kong

[KhrystinaM · Accepted solution]

Hello, I was able to get the data needed by creating a new column (combining date&department) and then creating a distinct count measure. Thank you for your assistance. 

[v-kongfanf-msft]

Hi @KhrystinaM ,

Change your formula like below:

Not Posted =
CALCULATE (
    DISTINCTCOUNT ( 'Status Board'[EntryID] ),
    FILTER ( 'Status Board', 'Status Board'[Posted] = "Not Posted" ),
    ALLEXCEPT ( 'Status Board', 'Status Board'[Department], 'Status Board'[Date] )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KhrystinaM]

I tried that but it returned the same value as my current measure. 

Ideally these 7 entries that missed the deadline on 1/16/24 could only list show not posted count as 1 (not 7).