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.
Solved! Go to Solution.
Yes, I think this is possible. We could take use of the Combo Chart to compare this year sales and last year sales, also the change percentage:Tutorial: Combo Chart in Power BI
For example, assuming here we have two tables, one for the sales (sales_USD), and another for the time table (DateTime). Sales table contains the sales and the corresponding sales’ date; the date table is a special table built to help calculate.
we could create two measures for the SUM of sales of this quarter and the same quarter last year, another measure to calculate their changes.
This year sales:
[ThisQsales] := CALCULATE(
SUM(Sales_USD[SalesAmount_USD]),
DATESQTD(DateTime[Date])
)
Last year sales:
[LastyQsales] := CALCULATE(
SUM(Sales_USD[SalesAmount_USD]),
DATESQTD(
SAMEPERIODLASTYEAR(DateTime[Date])
)
)
The change measure:
[Changes] := DIVIDE(
[ThisQsales] - [LastyQsales],
[LastyQsales]
)
Once created, put the [LastyQsales] and [ThisQsales] into column values, put the [changes] into line values.
See my example:
Regarding more information about data modeling, please check:Modeling
If you have any further questions, please feel free to ask.
Regards,
Charlie Liao
Yes, I think this is possible. We could take use of the Combo Chart to compare this year sales and last year sales, also the change percentage:Tutorial: Combo Chart in Power BI
For example, assuming here we have two tables, one for the sales (sales_USD), and another for the time table (DateTime). Sales table contains the sales and the corresponding sales’ date; the date table is a special table built to help calculate.
we could create two measures for the SUM of sales of this quarter and the same quarter last year, another measure to calculate their changes.
This year sales:
[ThisQsales] := CALCULATE(
SUM(Sales_USD[SalesAmount_USD]),
DATESQTD(DateTime[Date])
)
Last year sales:
[LastyQsales] := CALCULATE(
SUM(Sales_USD[SalesAmount_USD]),
DATESQTD(
SAMEPERIODLASTYEAR(DateTime[Date])
)
)
The change measure:
[Changes] := DIVIDE(
[ThisQsales] - [LastyQsales],
[LastyQsales]
)
Once created, put the [LastyQsales] and [ThisQsales] into column values, put the [changes] into line values.
See my example:
Regarding more information about data modeling, please check:Modeling
If you have any further questions, please feel free to ask.
Regards,
Charlie Liao
Hi Charlie,
I wish to achieve something similar as what Kyiyer did, therefore, I tried your formulas. However, I only got no data at all. Then I made the following change by replacing DATESMTD(DateTime[Date]) with Sales_USD[Date]. Unfortunately, it only showed the sales of August.
[ThisMsales] := CALCULATE(SUM(Sales_USD[SalesAmount_USD]), DATESMTD(Sales_USD[[Date]))
Same as [LastyMsales] := CALCULATE(SUM(Sales_USD[SalesAmount_USD]), DATESMTD(SAMEPERIODLASTYEAR(Sales_USD[Date])))
[Changes] := DIVIDE( [ThisMsales] - [LastyMsales], [LastyMsales])
Any idea what I might have done wrong? Thanks in advance.
Jane
@janez599did you manage to resolve this issue? As I have a similar issue in that it keeps returning a blank result.
many thanks
Victoria
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 | |
101 | |
86 | |
64 |