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,
My data is in a simple model with a sales table like below, and for each client I want to calculate 2021 sales only if they have 0 sales for 2022 and vice- versa.
I also need to be able to aggregate this up to Website level
Website | Client | Product | Date | Amount |
WebA | ClientA | ProductA | 01/01/2021 | 85 |
WebB | ClientA | ProductB | 01/01/2021 | 17 |
WebB | ClientB | ProductC | 01/01/2021 | 82 |
WebA | ClientC | ProductD | 01/01/2021 | 65 |
WebA | ClientA | ProductE | 01/01/2022 | 69 |
Solved! Go to Solution.
Hi @Atifz ,
This is my test table:
Please ccreate two measures:
Sum_of_client =
var sum2021 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Client] = SELECTEDVALUE('Table'[Client]) && YEAR('Table'[Date]) = 2021))
var sum_amount = CALCULATE(MIN('Table'[Amount]),FILTER('Table','Table'[Client] = SELECTEDVALUE('Table'[Client]) && YEAR('Table'[Date]) = 2022))
return
IF(sum_amount = 0,sum2021)
Sum_of_website =
var sum2021 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Website] = SELECTEDVALUE('Table'[Website]) && YEAR('Table'[Date]) = 2021))
var sum_amount = CALCULATE(MIN('Table'[Amount]),FILTER('Table','Table'[Website] = SELECTEDVALUE('Table'[Website]) && YEAR('Table'[Date]) = 2022))
return
IF(sum_amount = 0,sum2021)
Then you can calculate Previous year sales if current year is 0.
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Atifz ,
This is my test table:
Please ccreate two measures:
Sum_of_client =
var sum2021 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Client] = SELECTEDVALUE('Table'[Client]) && YEAR('Table'[Date]) = 2021))
var sum_amount = CALCULATE(MIN('Table'[Amount]),FILTER('Table','Table'[Client] = SELECTEDVALUE('Table'[Client]) && YEAR('Table'[Date]) = 2022))
return
IF(sum_amount = 0,sum2021)
Sum_of_website =
var sum2021 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Website] = SELECTEDVALUE('Table'[Website]) && YEAR('Table'[Date]) = 2021))
var sum_amount = CALCULATE(MIN('Table'[Amount]),FILTER('Table','Table'[Website] = SELECTEDVALUE('Table'[Website]) && YEAR('Table'[Date]) = 2022))
return
IF(sum_amount = 0,sum2021)
Then you can calculate Previous year sales if current year is 0.
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Atifz Well, maybe something like:
Measure =
VAR __Client = MAX('Table'[Client])
VAR __Table2021 = FILTER('Table',YEAR([Date])=2021 && [Client] = __Client)
VAR __Table2022 = FILTER('Table',YEAR([Date])=2022 && [Client] = __Client)
RETURN
IF(ISBLANK(__Table2022),SUMX(__Table2021,[Amount]), SUMX(__Table2022,[Amount]))
Thanks for this but I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".
@Atifz My bad:
Measure =
VAR __Client = MAX('Table'[Client])
VAR __Table2021 = FILTER('Table',YEAR([Date])=2021 && [Client] = __Client)
VAR __Table2022 = FILTER('Table',YEAR([Date])=2022 && [Client] = __Client)
RETURN
IF(COUNTROWS(__Table2022) + 0 = 0,SUMX(__Table2021,[Amount]), SUMX(__Table2022,[Amount]))
@Greg_Deckler Thanks for this, it's working now at client level but the totals are not correct. If I want to summarise at a website level, the numbers don't match
@Atifz See this: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community
Also: This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |