topic Challenge with Month-Year filtering/order without creating new in DAX Commands and Tips

Challenge with Month-Year filtering/order without creating new

al1981 — Wed, 18 Sep 2024 14:48:27 GMT

If I am correct, this is more specific to Power Pivot (where my data are located) and not PBI where this maybe is not issue.
I have dim table "plan per employee" for each quarter. In this plan, I would like to count added new customers to the the plan during quarter and to show it per employee by quarter/by month by correct month order. Issue I have, and don't know how to bypass it without to create new dim table with Month-Year (I already have one dim Month-Year which is filtering dim Date for many other calculation and cannot filter "plan per employee with active connection):
How would I be able to count added customers per quarter and show it in correct month order with the already created Monht-Year table? Once I created new DimTable Month-Year I can simply do OrderB in data model and as this table directly filter "plan per employee" I don't have any issues. Would this somehow be possible with already existing "old" Month-Year table with inactive connection in order to decrease number of Dim Tables?
for now I have simply calculation COUNTA([DateAdded]), i tried with some DAX, but i am lost... Thank you to anyone who could help me out...

Re: Challenge with Month-Year filtering/order without creating new

dharmendars007 — Wed, 18 Sep 2024 14:59:58 GMT

Hello al1981 ,

1. For getting the right moth order, If your Month-Year is stored as a text (e.g., "Jan 2023", "Feb 2023"), and it’s not ordering correctly, you can use the SORTBYCOLUMN feature in your model. Ensure your Month-Year column is sorted by an integer field such as a Month-Year Index (e.g., "202301", "202302"). This can ensure proper chronological sorting.

2. Using this measure you can re-activate the relationship between Dates table and Plan table CALCULATE(COUNTA('PlanTable'[DateAdded]),USERELATIONSHIP('DimDate'[MonthYear], 'PlanTable'[MonthYear]))

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

Re: Challenge with Month-Year filtering/order without creating new

al1981 — Wed, 18 Sep 2024 15:15:35 GMT

Dear dharmendars007

THANK you! Yes, i was playing wiht similar... but now it obivous how green i am 🙂 Thank you, its great! I assume i simple ignor message "Relationship between tables migh be needed" ....