Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Use calculate to work out previous years instance

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Not able to use dates unfortunatly can only use the instance

Abduvali's avatar
Abduvali
Icon for Skilled Sharer rankSkilled Sharer
9 years ago

and what does instance represent in your report?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

     

    thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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