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.
I am looking for a measure to compute Sales in a specyfic scenario.
When you look closer at my data set (call it Table1) you may notice 2018 is not closed yet.
The latest MONTH for 2018 is 4 whilst for 2017 I have full data for all 12 months.
I would like to compare 2018 Sales with analogical period of 2017
I mean, sales for 1-4 months of 2018 with sales for 1-4 months of 2017.
A trick is my Table1 is not fixed and will grow when new data for future months of 2018 will come.
Here is the data set:
Table1
YEAR | MONTH | SALES |
2017 | 1 | 100 |
2017 | 2 | 222 |
2017 | 3 | 102 |
2017 | 4 | 123 |
2017 | 5 | 322 |
2017 | 6 | 105 |
2017 | 7 | 106 |
2017 | 8 | 323 |
2017 | 9 | 108 |
2017 | 10 | 444 |
2017 | 11 | 545 |
2017 | 12 | 344 |
2018 | 1 | 110 |
2018 | 2 | 245 |
2018 | 3 | 112 |
2018 | 4 | 120 |
Here is what I have so far:
Sales Value = SUM(Table1[SALES])
and my current output:
The current output is not exactly what I need, because I can not compare totals.
I can not compare sales for 12 month to 4 ones. It simply makes no sense.
This is my desirabe output:
I want my measure to filter MONTHs for the latest year only and compare with the same period previous years.
I do not want any slicers for that. I do not want to show 5-12 months of 2017 because they have no counterparts in 2018.
Each time when Table1 is updated with latest month I would like to have it inclued in my output.
Hope it is clear. Hope someone can help
Solved! Go to Solution.
Just correcting the previous answer please do the following
Create a new column as
Date = date(Table1[YEAR],Table1[MONTH],"01")
and another column as
SalesYTD = var maxmonth = month(max(Table1[Date])) return if(Table1[MONTH] <= maxmonth ,Table1[SALES],0)
You can now use the column SalesYTD for your calculation
Hi @Anonymous,
You may refer to my solution here.
Hope this helps.
Hi @Anonymous,
You may refer to my solution here.
Hope this helps.
Both ways works, tnx!
Hi,
I have solved it. Please allow me some time to share my solution with you.
you can recreate the measure as :
YTD_SUM =
maxmonth = month(max(date(year,month,"01"))) return
calculate(sum(table1[sales]),month<=maxmonth)
Hi Nippon,
Didn't you miss VAR function in this expresion?
Anyway I get "The MAX function only accepts a column reference as an argument."
In fact YEAR and MONTH columns have no Date formattting.
(custom calendar)
Just correcting the previous answer please do the following
Create a new column as
Date = date(Table1[YEAR],Table1[MONTH],"01")
and another column as
SalesYTD = var maxmonth = month(max(Table1[Date])) return if(Table1[MONTH] <= maxmonth ,Table1[SALES],0)
You can now use the column SalesYTD for your calculation
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |