DAX Required for New Dealer Count and Value for respective period

[dhanurjaya]

Dear All,

Please help me for write DAX for logic below....

Data availeble in system from FY 2023-24 (Month wise), FY 2024-25 (month wise) and FY 2025-26 ( month wise).

My finacial year is Apr to Mar.

1. DAX Logic for New Dealer:

    a. FTM New Dealer: Dealers Last Full Year Value is <=0 and Current Year value is >0. ( it should dynamic if i select Month Apr      then result show only for Apr. if i select May then result show only for May (excluding Apr).

b.YTD New Dealer: Dealers Last Full Year Value is <=0 and CY YTD value is >0.

Example New Dealer Summary:

LY FTM ND Count: ?

LY FTM ND Value: ?

CY FTM ND Count: ?

CY FTM ND Value: ?

LY YTD ND Count: ?

LY YTD ND Value: ?

CY YTD ND Count: ?

CY YTD ND Value: ?

2. DAX Required for Dropped Dealer:

Logic:

Dropped Dealer Cound : Dealers CY Value <=0 and sameperiod LY value >0. (for both FTM/YTD - dynamic)

Example Dropped Dealer Summary:

LY FTM Drop dealer Count: ?

LY FTM Drop dealer Value: ?

CY FTM Drop dealer Count: ?

CY FTM Drop dealer Value: ?

LY YTD Drop dealer Count: ?

LY YTD Drop dealer Value: ?

CY YTD Drop dealer Count: ?

CY YTD Drop dealer Value: ?

Best Regards,

Dhanurjaya

[v-pnaroju-msft]

Hi dhanurjaya,

We kindly ask if your query has been resolved. If you have found a solution, please share it with the community to help others facing similar issues. If the issue persists, please provide sample data illustrating your problem in a usable format without including sensitive or unrelated information. Also, specify the expected outcome based on the sample data. If our response was helpful, kindly mark it as the accepted solution and give kudos to assist other community members. For further queries, feel free to reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi dhanurjaya,

We kindly wish to inquire whether your query has been resolved. If you have found a solution, we request you to share it with the community to assist others who may be facing a similar issue.

In case the issue persists, please provide sample data that clearly illustrates your problem in a usable format (excluding screenshots). Kindly ensure that no sensitive or unrelated information is included. Additionally, please specify the expected outcome based on the provided sample data.

If you found our response helpful, we would be grateful if you could mark it as the accepted solution and provide kudos. This will help fellow community members facing similar challenges.

Should you have any further queries, please feel free to reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi dhanurjaya,

Thank you for your patience.

To address your query: Does the formula CALCULATE(SUM('Sales'[SalesAmount]), 'Calendar'[FiscalYear] = LY) < = 0 return the full-year sales for the last year (LY) or only for the selected month?
This formula returns the full-year sales for the last year, as it is filtered solely by FiscalYear and not by month.

We would like to kindly check whether your query has been resolved. If you have found a solution, we request you to share it with the community to assist others who may be encountering a similar issue.

If the issue persists, please provide sample data that clearly illustrates your problem in a usable format (not as a screenshot). Kindly ensure that no sensitive or unrelated information is included. Additionally, please indicate the expected outcome based on the provided sample data.

If you found our response helpful, we would be grateful if you could mark it as the accepted solution and provide kudos. This will help fellow community members facing similar challenges.

Should you have any further queries, please feel free to reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi dhanurjaya,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[dhanurjaya]

Hi,

Apologies for the delayed response. I have reviewed your measures, but I am having trouble understanding them.

Does CALCULATE(SUM('Sales'[SalesAmount]), 'Calendar'[FiscalYear] = LY) <= 0 return the full-year sales for last year, or just the sales for the selected month in last year?

The comparison of new customers for the current year with last year's full year is not an apples-to-apples comparison.

1. Logic for Month wise : customers last year full year sales value is <=0 and current year for the month value is >0 and current year till previous month value is <=0 (excluding Current month).

2. Logic for YTD: customers last year full year sales value is <=0 and current year YTD value is >0.

need Summary - 1
Month on Month Trend
Measure	Finacial Year (Apr-Mar)	Apr	May	Jun	Jul
New Customer Count	2023-24	1	1	0	1
New Customer Value	2023-24	0.84	0.36	0.00	0.27
New Customer Count	2024-25	3	0	1	0
New Customer Value	2024-25	0.43	0.00	0.28	0.00
YTD Trend
Measure	Finacial Year (Apr-Mar)	Apr	May	Jun	Jul
New Customer Count	2023-24	1	2	2	3
New Customer Value	2023-24	0.84	2.33	2.38	2.65
New Customer Count	2024-25	3	3	4	4
New Customer Value	2024-25	0.43	0.47	0.75	3.74

need Summary - 2	FTM Jun	YTD Jun
Measure	LY FTM	CY FTM	LY YTD	CY YTD
New Customer Count	0	1	2	4
New Customer Value	0.00	0.28	2.38	0.75

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.

[dhanurjaya]

Hi,

i am unable share data please find Logic & Required Summary. (Required All Measure in Dynamic)

1. Logic for Month wise : customers last year full year sales value is <=0 and current year month value is >0 and current year till previous month value is <=0.

2. Logic for YTD: customers last year full year sales value is <=0 and current year YTD value is >0.

need Summary - 1
Month on Month Trend
Measure	Finacial Year (Apr-Mar)	Apr	May	Jun	Jul
New Customer Count	2023-24	1	1	0	1
New Customer Value	2023-24	0.84	0.36	0.00	0.27
New Customer Count	2024-25	3	0	1	0
New Customer Value	2024-25	0.43	0.00	0.28	0.00
YTD Trend
Measure	Finacial Year (Apr-Mar)	Apr	May	Jun	Jul
New Customer Count	2023-24	1	2	2	3
New Customer Value	2023-24	0.84	2.33	2.38	2.65
New Customer Count	2024-25	3	3	4	4
New Customer Value	2024-25	0.43	0.47	0.75	3.74

need Summary - 2	FTM Jun	YTD Jun
Measure	LY FTM	CY FTM	LY YTD	CY YTD
New Customer Count	0	1	2	4
New Customer Value	0.00	0.28	2.38	0.75

[Ashish_Excel]

Hi,

I will need data to work with.

[v-pnaroju-msft]

Hi dhanurjaya,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thankyou, @DataNinja777, for your response.

Hi @dhanurjaya,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Please find attached the screenshot and the PBIX file which may assist in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will help other community members who may have similar queries.

Should you have any further questions, please feel free to contact the Microsoft Fabric Community.

Thank you.

[dhanurjaya]

hi,

result not sertified, i thin something wrong..

Logic : if X1234 dealers LY Full Year Value <=0 and CY Till Apr+May value is <=0 and CY FTM Jun value is >0 then NDNR for Jun.

i am unable find this logic in below dax

FTM ND =

 VAR SelectedMonth = MAX('Calendar Master'[Month])

 VAR CY = MAX('Calendar Master'[FY Year (BI)])

 VAR LY = CY-1

 RETURN

 CALCULATE(

    DISTINCTCOUNT('Dump - PRI'[Parent Customer]),

    FILTER(

        VALUES('Dump - PRI'[Parent Customer]),

        CALCULATE(SUM('Dump - PRI'[Sales Val. (Calc)]),'Calendar Master'[FY Year (BI)] = LY) <=0 &&

        CALCULATE(

            SUM('Dump - PRI'[Sales Val. (Calc)]),

            'Calendar Master'[FY Year (BI)] = CY,

            'Calendar Master'[Month] = SelectedMonth

        ) >0

    )

 )

please help

[DataNinja777]

Hi @dhanurjaya ,

To calculate new and dropped dealers dynamically in Power BI using DAX, based on financial years running from April to March, we can use a combination of CALCULATE, FILTER, and SUM or DISTINCTCOUNT depending on whether you're counting dealers or summing their values. The key logic is to compare the current year's values with the previous year's and identify whether a dealer is considered "new" (no sales in the previous year, but sales in the current period) or "dropped" (sales last year, but none in the current period).

For FTM New Dealer count in the current year:

CY_FTM_NewDealer_Count :=
VAR SelectedMonth = MAX('DateTable'[Month])
VAR CY = MAX('DateTable'[FinancialYear])
VAR LY = CY - 1
RETURN
CALCULATE(
    DISTINCTCOUNT('Sales'[DealerID]),
    FILTER(
        VALUES('Sales'[DealerID]),
        CALCULATE(SUM('Sales'[SalesAmount]), 'DateTable'[FinancialYear] = LY) <= 0 &&
        CALCULATE(
            SUM('Sales'[SalesAmount]),
            'DateTable'[FinancialYear] = CY,
            'DateTable'[Month] = SelectedMonth
        ) > 0
    )
)

For FTM New Dealer value in the current year, replace DISTINCTCOUNT with SUM:

CY_FTM_NewDealer_Value :=
VAR SelectedMonth = MAX('DateTable'[Month])
VAR CY = MAX('DateTable'[FinancialYear])
VAR LY = CY - 1
RETURN
CALCULATE(
    SUM('Sales'[SalesAmount]),
    FILTER(
        VALUES('Sales'[DealerID]),
        CALCULATE(SUM('Sales'[SalesAmount]), 'DateTable'[FinancialYear] = LY) <= 0 &&
        CALCULATE(
            SUM('Sales'[SalesAmount]),
            'DateTable'[FinancialYear] = CY,
            'DateTable'[Month] = SelectedMonth
        ) > 0
    )
)

For YTD New Dealer count in the current year:

CY_YTD_NewDealer_Count :=
VAR CY = MAX('DateTable'[FinancialYear])
VAR LY = CY - 1
RETURN
CALCULATE(
    DISTINCTCOUNT('Sales'[DealerID]),
    FILTER(
        VALUES('Sales'[DealerID]),
        CALCULATE(SUM('Sales'[SalesAmount]), 'DateTable'[FinancialYear] = LY) <= 0 &&
        CALCULATE(
            SUM('Sales'[SalesAmount]),
            'DateTable'[FinancialYear] = CY,
            'DateTable'[Date] <= MAX('DateTable'[Date])
        ) > 0
    )
)

The value version of the above just replaces DISTINCTCOUNT with SUM as before.

For FTM Dropped Dealer count in the current year:

CY_FTM_DroppedDealer_Count :=
VAR SelectedMonth = MAX('DateTable'[Month])
VAR CY = MAX('DateTable'[FinancialYear])
VAR LY = CY - 1
RETURN
CALCULATE(
    DISTINCTCOUNT('Sales'[DealerID]),
    FILTER(
        VALUES('Sales'[DealerID]),
        CALCULATE(
            SUM('Sales'[SalesAmount]),
            'DateTable'[FinancialYear] = LY,
            'DateTable'[Month] = SelectedMonth
        ) > 0 &&
        CALCULATE(
            SUM('Sales'[SalesAmount]),
            'DateTable'[FinancialYear] = CY,
            'DateTable'[Month] = SelectedMonth
        ) <= 0
    )
)

And again, swap in SUM for the value version. You can apply similar logic for YTD dropped dealers by using Date <= MAX('DateTable'[Date]) within each year. This will give you a clean way to dynamically compute new and dropped dealers based on fiscal year, specific month selection, or year-to-date range.

Best regards,