YTD count of unique users who have only one purchase intent code and whose status is "Approved"

[735Alka]

Dear Community Users ,

I have sales data in which Users generate purchase intent codes, like invoice numbers, and for each intent code sales are generated. A single user may generate multiple intent codes for sale, and approved intents are considered final sales.

Now, i have need to find YTD count of users who have only one purchase intent code and whose status is "Approved" and my FY starts from 1st april to 31 march.

User code, intent code, intent generated date, approval status are the columns in my data

I've tried this measure, but it doesn't work, giving me incorrect results

unique count = CALCULATE(DISTINCTCOUNT(Table[User Code]),Table[Approval STATUS]="Approved",DATESYTD(Calendar_new[Date],"31/03"),FILTER(Table,COUNT(Table[ PURCHASE INTENT CODE])=1))

Please help me with this
Thanks

[AnalyticsWizard · Accepted solution]

@735Alka 

It looks like there are a few adjustments needed in your DAX formula to accurately calculate the year-to-date (YTD) count of users who have only one purchase intent code with an "Approved" status. The issue in your original formula is likely in the way the filter for single intent codes per user is applied.

1. First, ensure that the filter context for 'Approved' intents and within the correct fiscal YTD is correctly set.

2. We need to correctly filter users who have exactly one 'Approved' purchase intent in the YTD window.

Here’s how you can adjust your measure:

Unique Count YTD =
VAR ApprovedIntents =
FILTER(
CALCULATETABLE(
DISTINCT(Table[User Code]),
Table[Approval STATUS] = "Approved",
DATESYTD(Calendar_new[Date], "31/03")
),
CALCULATE(COUNTROWS(Table), Table[Approval STATUS] = "Approved") = 1
)
RETURN
COUNTROWS(ApprovedIntents)

- VAR ApprovedIntents: This variable creates a table of distinct 'User Code' that have 'Approved' status within the fiscal YTD.
- FILTER(...): Filters this table to keep only those users who have exactly one approved purchase intent code within the fiscal YTD.
- COUNTROWS(ApprovedIntents): Counts the number of unique user codes that meet the above criteria.

This approach ensures that the count is only considering users with exactly one approved intent code within the fiscal year to date, starting from April 1st.

Test this measure in your report to see if it produces the correct results. Adjustments might be necessary depending on how your calendar table aligns with the fiscal year and the specifics of your data model.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[735Alka]

I appreciate your help. However, I have created a measure to find my exact value from the start of each FY to a selected date or month from the slicer.

#Unique Count YTD = CALCULATE([Unique Count YTD],DATESYTD(Calendar_new[Date],"31/03"))

[VillyMBI]

Hello @735Alka ,

Try following Measure.

Measure =

VAR StartDate = Date(2023, 04, 01) -- This should be starting date of the selected year

VAR EndDate = Date(2024, 03, 31) --- It should be your current date

VAR AggTab = SUMMARIZE(

                       FILTER(Data1, [STATUS] = "Approved" &&

                                     [PURCHASE INTENT DATE] >= StartDate &&

                                     [PURCHASE INTENT DATE] <= EndDate),

                       [User Code], "Content Cnt", COUNT(Data1[PURCHASE INTENT CODE]))

VAR Cnt  =  CALCULATE(

                       DISTINCTCOUNT([User Code]),

                      FILTER(AggTab, [Content Cnt] = 1)

                      )

RETURN Cnt

StartDate and EndDate are taken for testing.
Following is test data

Data1 = DATATABLE("User Code", String, "PURCHASE INTENT CODE", String, "PURCHASE INTENT DATE", DATETIME, "STATUS", STRING,

                  {

                    {"1", "1", "2024-01-01", "Approved"},

                    {"2", "2", "2024-02-02", "Approved"},

                    {"3", "3", "2024-02-03", "Approved"},

                    {"4", "4", "2024-01-04", "Approved"},

                    {"1", "5", "2024-02-05", "Waiting"},

                    {"2", "6", "2024-03-06", "Approved"},

                    {"3", "7", "2024-03-07", "Approved"},

                    {"5", "8", "2024-03-08", "Approved"},

                    {"1", "9", "2024-04-09", "Approved"},

                    {"2", "10", "2024-04-10", "Waiting"}

                  }

                  )

[735Alka]

Thanks, it works for the mentioned start date and end date, but I need YTD count for example, if I choose a month or date from slicer, I need it to give the value up to that month or date from starting date of FY , i.e. I need a dynamic measure.