DAX for transacting accounts

[dreycruz]

Hi! Need some help with dax to compute the number of transacting accounts where they have sales for the month, quarter, and year.

I have Sales table with the following columns: account name, sales, month, date, year

I already have this query. But if i make it into startofquarter, nothing changes with the result.

CALCULATE(

    DISTINCTCOUNT('Raw data'[UniqueAcctName]),

    FILTER(

        'Raw data',

        [MTD Sales] <> 0

    ),

    FILTER(

        ALL('Calendar'),

        'Calendar'[Date] >= STARTOFMONTH('Calendar'[Date]) &&

        'Calendar'[Date] <= ENDOFMONTH('Calendar'[Date])

    ),

    'Calendar'[Year] = "2025"

)

[v-tejrama · Accepted solution]

Hi @dreycruz ,

I successfully outlined the process for calculating Monthly, Quarterly, and Yearly Transacting Accounts in Power BI, where a transacting account is any account with non-zero sales in a specific period. Starting with a Raw data table containing UniqueAcctName, Sales, and Date, I created a Calendar table using CALENDAR(), then added Month, Quarter, and Year columns. After establishing a relationship between Raw data[Date] and Calendar[Date], I developed three DAX measures with CALCULATE and DISTINCTCOUNT, using FILTER along with MONTH, YEAR, and FORMAT to accurately reflect each time frame. By organizing Calendar[Quarter] above Calendar[Month] in the Rows of a matrix visual, I ensured months were grouped under their respective quarters.

The resulting output displays each month’s transacting account count nested under each quarter, with yearly totals calculated accurately. This approach enabled me to clearly and confidently visualize sales activity trends over time.

I’ve attached the .pbix file and screen short used in this test for your reference.

Thank you,

Tejaswi.

[v-tejrama]

Hi @dreycruz ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[v-tejrama]

Hi @dreycruz ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Thank you.

[v-tejrama]

Hi @dreycruz ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Cookistador]

Hi @dreycruz 

Your issue is not very clear, can you provide a small example of what you are trying to achieve ?

Maybe by sharing a dummy data sample, and showing what the final result should look like