YTD of a dynamic measure

[nanma94]

Each month we look at the rolling 3 months transactions to decide whats the total number of buying customers (if at least a unit is sold, this is an active buying customer). So here total nunmber of buying customers is a measure itself. 

M_AccountActiveCurrentMonth = if(Sales[M_Rolling3MonthTotalUnit]>0, 1, 0)

M_AccountActiveCountCurrentMonth = SUMX('SalesAccounts (buying acct)', [M_AccountActiveCurrentMonth])

Now I want to get YTD buying account growth comparing to last year YTD. How do I get to that?  - 

Thanks much!
NM

[v-shex-msft]

Hi @nanma94,

You can use below formula to get YTD total:

M_AccountActiveCountCurrentMonth =
VAR currentDate =
    MAX ( 'SalesAccounts (buying acct)'[Date] )
RETURN
    SUMX (
        FILTER (
            ALLSELECTED ( 'SalesAccounts (buying acct)' ),
            [Date] <= currentDate
                && YEAR ( 'SalesAccounts (buying acct)'[Date] ) = YEAR ( currentDate )
        ),
        [M_AccountActiveCurrentMonth]
    )

If not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

[nanma94]

@v-shex-msft, thank you for your reply! but I need to ask further as I didnt explain the scenario clear enough last time.

According to the business rule, the YTD buying accounts is defined as the average of monthly buying accounts up to the current month. Lets say if we are looking at May YTD buying accounts growth comparing to 2017 - 

it should be -  avg(2018 Jan thru May monthly total as below) / avg(2017 Jan thru May monthly total as below)

"Sales" is a transaction table that has dates. 

"Sales Accounts (buying acct)" is another table that takes all distinct sales accounts from "Sales" table. 

I already have these 2 measures below - 

M_AccountActiveCurrentMonth = if(Sales[M_Rolling3MonthTotalUnit]>0, 1, 0)

M_AccountActiveCountCurrentMonth = SUMX('SalesAccounts (buying acct)', [M_AccountActiveCurrentMonth])

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[nanma94]

Here we go, with data - 

https://drive.google.com/open?id=196MxtVW6RF5OLtK-62Ruw-KvAoyWAY7-

[Ashish_Mathur]

Hi,

What exacct result are you expecting.  Give me the exact numbers so that i can tally my formula's result with the numbers that you are expecting.

[nanma94]

@Ashish_Mathur, 

I was looking whole day but still struggling... Thank you for your help in advance:)

With this monthly accounts count above (as in the sample PBIX) , I am looking for 2018 May account growth as - 

divide

average YTD accounts in 2018: (2778+2709+3191+2778 + 3146) / 5

by

average YTD accounts in 2017: (926 + 995 + 1136 + 989 + 1010)/5

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this file.

Hope this helps.

[nanma94]

@Ashish_Mathur

I guess I got excited too early...

in the sample, it works. but when I port over to my real file, YTD doesnt do rolling, it show the same # as the monthly count. 

I guess my customer count is too complex and it reference dates multiple times. 

As you see I have sales table, and account table. If in sales, rolling 3 months unit sold >0, the account in the account table is considered active (given value 1 in the measure than 0). Then for each specific month, I add up all the 1s to get to total active account count. 

Coming to do YTD, the monthly account count is already a complex measure referencing date multiple times...

To test, when I am not using this complex account count in YTD, but rather using a simple total revenue/unit measure, the YTD formula works. 

[Ashish_Mathur]

There is no image there.  Share the link from where i can download your PBI file and clearly tell me where the problem is.

[nanma94]

@Ashish_Mathur

it surely works. Thank you so much. 

I was on DATESYTD at the very beginning but I was trying to put sumx in front of the measure and never got out of it...

Again, thank you so much Ashish!

NM

[Ashish_Mathur]

You are welcome.