Calculating sales variance with different criterias

[Anonymous]

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!!

[sturlaws]

Hi @Anonymous,

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.

[Anonymous]

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!

[Anonymous]

Hi!

Can someone give me a hand on this please?!

I am still stuck on this matter.

Thank you!

[V-lianl-msft]

Hi @Anonymous ,

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.