topic Re: Measure based on selected periods (year,quarter, month) and based on dates present in fact table in DAX Commands and Tips

Measure based on selected periods (year,quarter, month) and based on dates present in fact table

Anonymous — Fri, 28 Jan 2022 04:11:06 GMT

In PBI desktop...a big challenge Im facing...

I need to create a measure (# of Litigation Orders) that calculates a count where status=Litigation for each order in a given period. On the table we have a field named "start of litigation" and another field called "end of litigation" in the same "Orders table".

Imagine that I selected two separated periods in a date table hierarchy filter (2021,q1, m1) and (2021, q2, m5), considering that our date table has a physical relationship with the Orders table.

I'm eager to know how many orders were in Litigation at that periods using that measure in a line chart by Period, for example. What dax expression should I use to achieve my goal as fast as accurate?? Thanks in advance.

Re: Measure based on selected periods (year,quarter, month) and based on dates present in fact table

amitchandak — Fri, 28 Jan 2022 04:45:25 GMT

Anonymous , check if you need come think similar to active employees in this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Using a date table.

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Measure based on selected periods (year,quarter, month) and based on dates present in fact table

v-robertq-msft — Wed, 02 Feb 2022 08:02:54 GMT

Hi,

Have you followed the link posted by amitchandak to find the solution to your problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly

Best Regards,

Community Support Team _Robert Qin

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

Re: Measure based on selected periods (year,quarter, month) and based on dates present in fact table

Anonymous — Wed, 02 Feb 2022 11:51:52 GMT

It works! Thank you!