cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JCA
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.

 

Captura2.JPG

 
 

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:
 

Captura1.JPG

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
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 (
    ADDCOLUMNS (
        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 (
    ADDCOLUMNS (
        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.

JCA
Regular Visitor

Re: Calculating sales variance with different criterias

Hi @sturlaws 

 

Thank you for your reply!

 

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!

JCA
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
Microsoft

Re: Calculating sales variance with different criterias

Hi @JCA ,

 

See if this can help you:https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/ 

 

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

Helpful resources

Announcements
June 2020 Community Challenge: Can You Solve These?

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

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors