Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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?
I've tried this..
Payment_Counts2 =
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.
@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
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?
@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
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.
@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
@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
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |