cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculating sales variance with different criterias

Hi there,

I have created a table that shows \$sales /store, using a calendar filter from 1/jan 2019 to 31/dec 2019.

Yet, I want the table to show the \$sales/store SINCE the date each of them was reopened.

Lets say I have 3 stores (A,B,C), all with data previous 2017, BUT with reopened dates as follow:

A: reopened 1 july 2018

B: reopened 1 dec 2018

C: reopened 1 june 2019

As Stores A and B were reopened before the time period selected on the calendar filter (all 2019), the table will show their sales from 1/jan 2019-31/dec2019. In the case of Store C however, the table will show \$sales from 1/june2019-31/dec2019.

For this step I have created (succesfully), the following DAX:

RealSales = CALCULATE(SUM(Sales);Table[store]=A || Table[store]=B) + CALCULATE(SUM(Sales);Table[store]=C; Calendar[Date]>DATE(2019;05;31))

The problem comes when I try to calculate the Real Sales Variance. In this case, I would like the table to show thw following information:

For this purpose, I have been using the following DAX:

Var%Sales = [Real Sales] / CALCULATE([Real Sales];SAMEPERIODLASTYEAR(Calendar[Date])) -1

(I have also tried to replace SAMEPERIODLASTYEAR with DATADD)

The thing is, when I do this, the measure will succesfully calculate A and B sales variance, but not C (giving an empty result).

Which DAX can be useful to calculate each sales variance following the "reopened date" criteria for each store???!

Thank you all!!

4 REPLIES 4
Super User III

## Re: Calculating sales variance with different criterias

Hi @JCA,

There are many possibilities to solve this, but they are not quit straight forward.

Here is one option:

create the measure for sales this year like this:

``````This year =
SUMX (
VALUES ( dimShop[Shop] );
"reopened"; MIN ( dimShop[Reopen date] )
);
CALCULATE ( SUM ( Sales[sales] ); FILTER ( dimDate; dim[date] > [reopened] ) )
)
``````

and for previous year like this:

``````Previous year =
SUMX (
VALUES ( dimShop[Shop] );
"reopened";
VAR _isReopenedThisYear =
IF ( MIN ( dimShop[Reopened year] ) = MIN ( dimDate[Year] ); 1; 0 )
VAR _prevYearDates =
IF (
_isReopenedThisYear = 1;
DATE ( MIN ( dimShop[Reopened year] ) - 1; MONTH ( MIN ( dimShop[Reopen date] ) ); DAY ( MIN ( dimShop[Reopen date] ) ) );
DATE ( YEAR ( MIN ( dimDate[StartOfYear] ) ) - 1; 1; 1 )
)
RETURN
_prevYearDates
);
CALCULATE (
SUM ( Sales[sales] );
FILTER (
ALL ( dimDate );
dimDate[Date] >= [reopened]
&& dimDate[Date]
<= DATE ( YEAR ( MIN ( dimDate[StartOfYear] ) ) - 1; 12; 31 )
)
)
)
``````

As you can see from the code, some additional tables are needed: dimDate and dimShop. Here is the link to the sample report I have created: .pbix

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Regular Visitor

## Re: Calculating sales variance with different criterias

However, I am having some difficulties in doing what you suggested.

First of all, I followed the "This Year" meassure exactly as you wrote it, but the result shows me \$sales/store without giving notice to the "reopened date". I don´t know why, but although the DAX has no errors, there is something about the calendar date > reopened date that is not working.

As of the second calculation suggested for "Previous Year", I cannot use de STARTOFYEAR formula.

It seems that as I am in DirectQuery mode, this option is not available.

Is there another solution I can use of the other options?

Thank you again for your time!

Regular Visitor

## Re: Calculating sales variance with different criterias

Hi!

Can someone give me a hand on this please?!

I am still stuck on this matter.

Thank you!

Microsoft

## Re: Calculating sales variance with different criterias

Hi @JCA ,

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### June 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors