Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sort Order Impacting Measures

I have a dataset which has This Year and Year Ago data. I used a table to provide the sorting order for the same using a relationship and relation function. Post this I sorted TY/YA columns based on the new column with sort order. But as soon as I did that my change measures (TY/YA-1) stopped working and started giving me error. Has anyone faced a situation like this?

 

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , how is TY, LY created. Are they created using date calendar and common time intelligence? Using datesytd, totalytd. Example

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    I am not clear about your requirement and your data structure, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      My sample data looks like this:

      Time PeriodBrandSales
      TYB1100
      YAB1100
      YAB21110
      TYB2

      1100

      Hi

      Apologies for the bad explanation of the problem

       

      Situation:

      I was using a measure to calculate change in sales: Sales(TY)/Sales(YA)-1. It was working perfectly.

      Problem:

      However, when I was plotting a clustered column chart with brands in axis and TY/YA as legends and value. I couldn't control the TY/YA.

      Hence, I created a table with index of TY as 2 and YA as 1. I used related function to bring the index values to the sales table after creating relationship on Time Period. Then I sorted column Time Period on basis of Index. This is when my change measures stopped working and started giving me error.

       

      I am a newbie in DAX, please can you help me by pointing out what did I do wrong in this case?

       

      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi Anonymous , 

        I think this can't sort by legend column(it seems to be related to value and axis ). In addition, if possible, could you please inform me your expected output? Then I will help you more correctly.

         

        Please do mask sensitive data before uploading.

        Thanks for your understanding and support.
        Best Regards,
        Zoe Zhi

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