cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kyiyer Frequent Visitor
Frequent Visitor

How to show sales for current and previous quarters with change in a tile?

Hi

Managed to publish a simple dashboard to PowerBI in about two hours. Awesome tool.

Is it possible to show this quarter's sales and the same quarter's sale from last year with a change percentage field as well? An arrow like stock price would be really nice.

My data comes from an Excel file that has the figures.

Is this information enough?

Many thanks

Iyer
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: How to show sales for current and previous quarters with change in a tile?

@Kyiyer

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:
Capture.PNG

 

Regarding more information about data modeling, please check:Modeling

 

If you have any further questions, please feel free to ask.

 

Regards,

Charlie Liao

3 REPLIES 3
Moderator v-caliao-msft
Moderator

Re: How to show sales for current and previous quarters with change in a tile?

@Kyiyer

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:
Capture.PNG

 

Regarding more information about data modeling, please check:Modeling

 

If you have any further questions, please feel free to ask.

 

Regards,

Charlie Liao

janez599 Frequent Visitor
Frequent Visitor

Re: How to show sales for current and previous quarters with change in a tile?

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

Highlighted
vcmoffatt Frequent Visitor
Frequent Visitor

Re: How to show sales for current and previous quarters with change in a tile?

@janez599did you manage to resolve this issue? As I have a similar issue in that it keeps returning a blank result.

 

many thanks

Victoria