Dax query to calculate YTD based on Yearly total IDs not based on latest or selected Month ID's

[Dilip7]

Dax query to calculate YTD based on Yearly total IDs not based on latest or selected Month ID's

for example :

Jan 24 have 200 Agents

Feb 24 have 230 Agents

Mar 24 have 210 Agents

so when we calculate YTD it should segregate agents not only based on Mar 210 it should consider all distinct agents across the year .

i tried creating composite key use agent and month year but this gives output based on Mar 24 agents not overall distinct agents .

pls let me know if any solution

[Dilip7]

its not working for me .

[Anonymous]

Hi @Dilip7 ,

Could you please provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[rajendraongole1]

Hi @Dilip7  - you need to ensure that the calculation considers all agents across all months up to the current month

measure eg. as follow

YTD Distinct Agents =
VAR CurrentYear = YEAR(MAX('Table'[Date]))
RETURN
CALCULATE(
DISTINCTCOUNT('Table'[AgentID]),
FILTER(
ALL('Table'),
YEAR('Table'[Date]) = CurrentYear &&
'Table'[Date] <= MAX('Table'[Date])
)
)

check the above and let know.

[Dilip7]

Thank you @rajendraongole1 .I will check .

[rajendraongole1]

Please check and give a confirmation, Thank you!