Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Price Variance SQL Issue

I built a SQL Query that runs a price variance calculation correctly using the following logic: Price Variance = (Avg Price Current Year - Avg Price Previous Year) / Current Volume   This calculat...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

In short, yes.  You need a Dates/Calendar table.  You also need to do the calculation in DAX as a measure.  No need for that SQL query - you only need to provide the raw data.

 

Please provide sample data in usable format (not as a picture) and show the expected outcome.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Sorry for the wait for the reply. The person who requested it originally brought it back up again. There is a file here with some sample data.

    https://drive.google.com/file/d/1EEpP1HkAQc5P0MkazTu7-J6raLocjEbl/view?usp=sharing

     

    I can look more to into that using the logic you mentioned. I tried to calculate a True False column though that determines if a Customers Create Date is between the dates I selected. Any idea how to make those last dates True and the rest False?

    Guess I am missing the right DAX syntax to pick up the dates I filter in the Date Table to drive a calculatation in my Current Variance Table. 

     

    Also yes the tables are the same. They want to filter two periods of time essentially and then run calculations on any time the customer and item have a transaction within the same month which is why I was trying to filter both and then join the Current Variance and Previous variance tables on customer and item.