Need Help to write Dax.

[adeeln]

I have Total Open Opportunity Count Measure and Date Dimention table that based on closed date.I need overdue,0-30 days,1-3 month,3-6 months,and >6 month measures for total open opportunities.

Link of my Report

• Link

[MFelix]

Hi @adeeln ,

The link is asking for a password to acces the folder and download the file.

[adeeln]

@MFelix Please try drive Link.

[MFelix]

Hi @adeeln ,

Both of them are asking for password.

[adeeln]

Hey @MFelix,

Can you please check now? It should work. 

[MFelix]

Hi @adeeln ,

Believe your issue is related with the filter part of the formula where you are picking up the last date, since you have no filter on it will pick the maximum value of the calendar date that is 26/12/2020 if you look a your data the last value for close date is 26/11/2020 so it's only picking 1 value.

Try to correct your measure to:

Nb Of Open Opportunities 0 to 30D =
CALCULATE (
    [Nb Of Open Opportunities];
    FILTER (
        ALLSELECTED ( Opportunity[ClosedDate] );
        Opportunity[ClosedDate] <= MAX ( DimDate[Date] )
            && Opportunity[ClosedDate]
                >= MAX ( DimDate[Date] ) - 30
    )
) + 0

Check if this is working changing the -30 to -60 and so on.

[v-deddai1-msft]

Hi @adeeln ,

Would you please explain more about your requirements?  I am sorry that I am not familiar with Open Opportunity. Could you please explain your expected output in detail?

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

Best Regards,

Dedmon Dai