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

[Anonymous]

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?

[v-xiaotang · Accepted solution]

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.

[daXtreme]

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 🙂

[amitchandak]

@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