Matrix Including Values Which Are Not Relevant due to Date Calculation - How to Exclude?

[cbarker]

I have a matrix table that ultimately is including values that have no data / relevant detail for the measures which are present in my values section. E.g. All customers are showing under every single rep, which should not be the case and should only reflect their current customer base.

I believe that this is being driven by my YTD / MTD Working Days Complete to Date measure as when I remove this the matrix is then reflected correctly with the appropriate values. 

My date measure is as follows;

YTD Work Days Compl = CALCULATE(COUNTROWS(DimDate),FILTER(DimDate,DimDate[Date]<=TODAY()-1),FILTER(DimDate,DimDate[Year]="2024"),DimDate[Work Day]=1)

I've been trying to add this detail to a calculated column to return the correct MTD days to date which have been completed, but I am unable to get back to this value which the measure is returning.

Anyone have any thoughts?

[v-tangjie-msft]

Hi @cbarker ,

You can create a calculated column.

YTD Work Days Compl =
COUNTROWS (
    FILTER (
        DimDate,
        DimDate[Date]
            <= TODAY () - 1
            && DimDate[Year] = "2024"
            && DimDate[Work Day] = 1
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Ritaf1983]

Hi @cbarker 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523