topic Re: Calculate stock Number of current contracts per month with a visual table. in DAX Commands and Tips

Calculate stock Number of current contracts per month with a visual table.

Anonymous — Thu, 08 Sep 2022 07:35:28 GMT

I would like to know how to calculate the number of current contracts of a customer with a visual table. I have made a copy of the table on the image below. This table is a filtered table. But with this copy, the dax formula does just fine. Only I don't know how to get this table in the formula.The calculation to count the contracts I have:

Number of current contracts per month =

VAR MinDatum2 = MAX(Dimdate_end[Date])

VAR MinDatum3 =MIN(Dimdate_end[Date])

RETURN

CALCULATE(

COUNTROWS('Tabel (2)'),
Dimdate_end[Date] >= MinDatum3 ,

Dimdate[Date] <= MinDatum2

)

I would like to use the table on the picture for my calculation. I probably have to build a visual table for this because the table on the picture is filtered. This table normally consists of 400 records. Because I use an earlier function to calculate the sum of hours of a current contract in the data. An index is placed on it and at the lowest in index is the sum of the number of hours of the contract. I use that to determine the contract.

To determine this I use the following dax formula:

Contract >4 hours = If(Data[Index traject] = 1 &&
Data[Traject contract hours] >= 4,1,0)

But how can I Calculate stock Number of current contracts per month with a visual table?

Re: Calculate stock Number of current contracts per month with a visual table.

amitchandak — Thu, 08 Sep 2022 08:12:35 GMT

Anonymous , refer if one the approach can help

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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Between Dates - Dates between
Tables way
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Re: Calculate stock Number of current contracts per month with a visual table.

daXtreme — Thu, 08 Sep 2022 08:28:11 GMT

Hi there.

To me, it's not really clear what it is you want. Can you please simplify your question? Thanks. And please always remember that a picture is worth a thousand words 🙂

Re: Calculate stock Number of current contracts per month with a visual table.

v-xiaotang — Wed, 14 Sep 2022 09:33:26 GMT

Hi Anonymous

Thanks for reaching out to us.

>>But how can I Calculate stock Number of current contracts per month

please try this measure,

measure = CALCULATE ( COUNTROWS ( Data ), FILTER ( Data, [Contract >4 hours] = 1 && YEAR ( Data[date] ) = YEAR ( MIN ( Dimdate[Date] ) ) && MONTH ( Data[date] ) = MONTH ( MIN ( Dimdate[Date] ) ) ) )

Best Regards,

Community Support Team _Tang

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