Forum Discussion

Mk60's avatar
Mk60
Resolver I
2 years ago
Solved

Period over Period variance

Looking for a DAX calculation to calculate change in dollars, (variance) for Metrics 1-5 in column A, from quarter to quarter to get values in columns E and F for each metric in column A and Total as well. Please advise. Thanks in advance!

 

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    remove the DIVIDE part and just keep the difference part.

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please consider using the Quick Measure that was designed for this.

    • Mk60's avatar
      Mk60
      Resolver I
      2 years ago

      I looked at that but it gives me only percent, I could not find option to get dollar variance? Thanks for your note though

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        remove the DIVIDE part and just keep the difference part.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    You cannot get 2 total columns (E and F) in a table/matrix visual.  You can show one 1 variance column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Mk60 ,
    Thanks for lbendlin  and lbendlin  response. Here are my additional dax expressions and examples
    Sample data

    Create measures

    Q2-Q3 = SELECTEDVALUE('Table'[23-Sep])-SELECTEDVALUE('Table'[23-Jun])
    Q3-Q4 = SELECTEDVALUE('Table'[23-Dec])-SELECTEDVALUE('Table'[23-Sep])

    Final output

     

    Best regards,

    Albert He

     

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

     



     



    • Mk60's avatar
      Mk60
      Resolver I
      2 years ago

      Hi Albert,

      Thanks so much for your sample. I was hoping your solution was exactly what I need, but Unfortunately, I just can't make it work for me, and wonder if you could help me how to actually pick the single date in this DAX formula? I think what you have in your example as "[23-Sep]" is a Date column value in my table named "Query1". If I follow your calc I just cannot select unique date "23-Sep" since DAX is suggesting .[Month], or .[Quarter] or day. I tried to manually type it but that's not working. Maybe if I attach this picture of my column it might help you see the issue? As you can see my Date is coming from my calulated column named "Posting DATE SHORT", which I created from the existing date filed "Posting Date" in my Query1 table. Your time and willingness to help is much appreciated!