Use calculate to work out previous years instance

[Anonymous]

Hi,

I am wanting to use a variance against the previous year but I am unable to do same period last year as I am going of a custom column called instance rather an a date.

The instance is the instance of the Monday in the month so for example comparing the first instance of Monday Ujly against the first Monday of July last year.

So I am wanting to have a variance column comparing for example 2017 to 2016.

[Abduvali]

@Anonymous

I think if you just create a new measure with following calculation (2016 Total - 2017 Total) to what you already have and you will get your variance column, and you can filter by anything you want it will do the calculation for you.

[Abduvali]

Hi @Anonymous,

My understanding you trying to see the difference in sales between this year and the last year. I have replicated what you have you can find it in my library by clicking on the link below:

• Calculate Variance files

Hope that helps.

Regards

Abduvali

[Anonymous]

Hi Abduvali,

Thanks for that, unfortunatly not what I am loking for.

I need to find a way to use the item selected from the slicer in a measure to calculate the sales against the previous year but what has been selected in the slicer.

Thanks

C

[Abduvali]

Try this out:

• PARALLELPERIOD Function (DAX)

[Anonymous]

Not able to use dates unfortunatly can only use the instance

[Abduvali]

and what does instance represent in your report?

[Anonymous]

The instance of the day in the month so for example the first monday would be 1 and the second Monday 2

thanks

[magsod]

Maybe not the most elegant solution but this should give you something you could use.

Define a new measure for:

Sales same instance last year =
CALCULATE (
    SUM ( Sales[Amount] );
    VALUES ( 'Calendar'[instance number] );
    VALUES ( 'Calendar'[Month] );
    VALUES ( 'Calendar'[Weekday] );
    FILTER ( ALL ( 'Calendar' ); 'Calendar'[Year] = MIN ( 'Calendar'[Year] ) - 1 )
)

BR,

Magnus

[Abduvali]

Try this out:

PARALLELPERIOD Function (DAX)