Dynamic measure calculation

[FrancescoSCP]

Hi all,

i need your help!

i have a "Payments" table  with some fields.

In Power BI i calculate a new column called "Fiscal Year" with this formula:

IF(MONTH(Payments[Date].[Date])< 9;YEAR(Payments[Date].[Date]);YEAR(Payments[Date].[Date]) +1)

Now i have to calculate New customers. I count the CustomerID in the Payments table: if it's >1 it's an "old"

customer, in the other way a new one.

The formula that i've used in measure is:

Payment_Counts = CALCULATE(COUNTROWS(Payments);DISTINCT(Payments[CustomerID]))

And in the column:

IF([Payment_Counts ] = 1; "New";"Old")

Everything works good if i analyze this year, but if i filter Fiscal Year

2017 (for example) some customer that were new now are considered "old" because they have paid in 2018 or 2019.

The goal is to watch the data like a photo in 2017. So i think i have to filter the Payments table to Fiscal Year less

or equal than the slicer selection.

Is there any way to do this?

Thanks in advance.

Francesco

[Ashish_Mathur]

Hi,

How do you identify a new customer?  If a customer's ID appeared in the Table in 2017, did not appear in 2018 and then reappeared in 2019, then would that customer be a new customer in 2019?

[FrancescoSCP]

I've tried this..

Payment_Counts2 =

VAR FiscalYear_var = SELECTEDVALUE(Payments[FiscalYear])

RETURN

CALCULATE(COUNTROWS(FILTER(Payments;Payments[FiscalYear]<=FiscalYear_var));DISTINCT(Payments[CustomerID]))

And the column like this:

IF([Payment_Counts2] = 1; "New";"Old")

I created tho charts with the new measure and the old one but nothing changes, i've got the same values.

Any suggestions?

[v-lid-msft · Accepted solution]

Hi @FrancescoSCP ,

The measure is dynamic but the calculated columns are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated column dynamically.

But we can create the calculated column use following formula to show the status based on the year.

Column =
VAR c = [CustomerID]
VAR y = [FiscalYear]
RETURN
    IF (
        COUNTROWS ( FILTER ( 'Payments', [CustomerID] = c && [FiscalYear] < y ) ) > 0,
        "Old",
        "New"
    )

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FrancescoSCP]

@v-lid-msft I will give a try to your suggestion, thanks!

@Ashish_Mathur No, if a customer paid in 2015 and then in 2019, in this year he is an "Old" one.

Regards,
Francesco

[Ashish_Mathur]

Hi,

Instead of calculating a new column in your base data with New/Old, would you be OK with 2 measures being created in your visual - one for new and another for old?

[FrancescoSCP]

@Ashish_MathurNo i need a column beacuse i need to create a graph splitted by new/old with customer count.

@v-lid-msftI've tried your suggestion and something seems to work. For the FiscalYear filter i need to filter Year less or equal than, but if i change the formula from < to <= it shows no results. What am i doing wrong?

Thanks,

Francesco

[v-lid-msft]

Hi @FrancescoSCP ,

Based on my test, it works on my shared sample data, but if a customer buy in 2017 and 2019, then we filter the year as greater than 2018, what kind do you want to assign it to?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FrancescoSCP]

@v-lid-msfti'll try to explain...

If a customer paid in 2017 and then in 2019, if i select 2019 he is an old one, but if i select 2017 he is new.

So your formula works good, but in the FILTER function i need to filter FiscalYear <= y (the original one is FiscalYear < y).

But if i change the filter to <= i have no values.....

Thanks,

Francesco

[FrancescoSCP]

@v-lid-msftit seems to work with sample data, but not with the real data.. i'm doing exact the same expect one thing: with sample data i have already year column, in my production data i calculate FiscalYear column.

Regards,

Francesco

[FrancescoSCP]

@v-lid-msftyes of course, i've changed from 0 to 1 but i get no data! I don't know why.

I will extract some sample data from my database and try to replicate this scenario.

EDIT: it seem to work now..i will check data with customer and let you know.

Thanks,

Francesco

[FrancescoSCP]

@v-lid-msft  Thanks for the tip it works now!

Francesco

[v-lid-msft]

Hi @FrancescoSCP ,

Column = 
VAR c = [CustomerID]
VAR y = [FiscalYear]
RETURN
    IF (
        COUNTROWS ( FILTER ( 'Payments', [CustomerID] = c && [FiscalYear] <= y ) ) > 0,
        "Old",
        "New"
    )

But if we use the <= in formula, In current year, the customer will be assigned as old, we need to change the 0 to 1, like following formula:

Column = 
VAR c = [CustomerID]
VAR y = [FiscalYear]
RETURN
    IF (
        COUNTROWS ( FILTER ( 'Payments', [CustomerID] = c && [FiscalYear] <= y ) ) > 1,
        "Old",
        "New"
    )

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.