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.
Hello Devs,
Here is my problem. I have two measures. One for This Year total sales and the other for last year sales. I have different stores and want to count the number of stores which have a negative difference.
sales TY =
sum(
'sales'[sales]
)
sales LY =
calculate(
[sales TY],
sameperiodlastyear(
'date'[date_key]
)
)
diff =
[sales TY]-[sales LY]
I tried using the following meassure
Negative stores =
calculate(
count('stores'[store_key]),
filter(
all('sales'),
[diff]<0)
)
but i get the following result
thanks
Reynaldo
Solved! Go to Solution.
Hi @reynaldo_malave . Try this...
VAR StoreSummary =
SUMMARIZE(
VALUES('stores'[store_key]),
"CurrentYearSales",
[Sales TY],
"PriorYearSales",
[Sales LY]
)
RETURN
CALCULATE(
COUNT('stores'[store_key]),
FILTER(
StoreSummary,
[PriorYearSales] > [CurrentYearSales]
)
)
Hi @reynaldo_malave . Remember when I said I was surprised it was correct because I wrote it in Notepad? 🙄
There were two issues. First, the measure for [Sales TY] is returning all sales regardless of year (unless it's filtered by year). The second is my measure left out the Store Key in the summary table variable.
Total Sales = SUM(Sales[sales])
Sales TY =
TOTALYTD(
[Total Sales],
'Calendar'[Date]
)
Sales LY =
CALCULATE(
[Sales TY],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
-- Used to check the counts only
Sales YOY Change = [Sales TY] - [Sales LY]
Negative Stores =
VAR StoreSummary =
SUMMARIZE(
VALUES('stores'[store_key]),
Stores[store_key],
"CurrentYearSales",
[Sales TY],
"PriorYearSales",
[Sales LY]
)
RETURN
CALCULATE(
COUNT('stores'[store_key]),
FILTER(
StoreSummary,
[PriorYearSales] > [CurrentYearSales]
)
)
Hi @reynaldo_malave . Try this...
VAR StoreSummary =
SUMMARIZE(
VALUES('stores'[store_key]),
"CurrentYearSales",
[Sales TY],
"PriorYearSales",
[Sales LY]
)
RETURN
CALCULATE(
COUNT('stores'[store_key]),
FILTER(
StoreSummary,
[PriorYearSales] > [CurrentYearSales]
)
)
De nada! Glad I could help. Between us...surprised it's correct since I wrote it in Notepad. 😉
@littlemojopuppy hey man, sorry to bother like this but taking a better look at the measure i must say it did not work. It is just returning the total amount of stores in my stores table. I was so exited yesterday that got a number instead of an error or blank that I accept it as a solution and finish my days work.
@reynaldo_malave No worries! 🙂
Can you give me some raw data to work with and I'll figure this out
@littlemojopuppy you can download it from here. The correct answer should be 4. Thanks
https://drive.google.com/file/d/1VQNfOtFrIJu_maXjFqJeoxTiNz0X0R_3/view?usp=sharing
Hi @reynaldo_malave . Remember when I said I was surprised it was correct because I wrote it in Notepad? 🙄
There were two issues. First, the measure for [Sales TY] is returning all sales regardless of year (unless it's filtered by year). The second is my measure left out the Store Key in the summary table variable.
Total Sales = SUM(Sales[sales])
Sales TY =
TOTALYTD(
[Total Sales],
'Calendar'[Date]
)
Sales LY =
CALCULATE(
[Sales TY],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
-- Used to check the counts only
Sales YOY Change = [Sales TY] - [Sales LY]
Negative Stores =
VAR StoreSummary =
SUMMARIZE(
VALUES('stores'[store_key]),
Stores[store_key],
"CurrentYearSales",
[Sales TY],
"PriorYearSales",
[Sales LY]
)
RETURN
CALCULATE(
COUNT('stores'[store_key]),
FILTER(
StoreSummary,
[PriorYearSales] > [CurrentYearSales]
)
)
@littlemojopuppy You are a life saver. Thanks for taking the time. I did learn how to summarize. The hard way!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |