Forum Discussion

Willborn's avatar
Willborn
Advocate III
10 years ago

Measure increase/decrease of quote deliveries by month/s in %

Dear Colleagues

 

I need to do a POC till Monday, showing the deviation of quotes sent to the customer.

What I did so far is to join the tables from SAP containing the quote “created date/time” and the time stamp of the printout, calculated the days and changed this information into text within a new column. Result is shown on the screen.

 

At this time, we know now by filtering to the month, how many quotes we have sent within 1, 1-3 or more days.

What I need now is the deviation from this month to the last month, or last 3 month etc.

 

Means: Count the “printed = 1 day in March” vs. “printed = 1 day in the month before” > in % increasing/decreasing…(?!)

Guess I have to write a measure for that – counting the values and showing the deviation in a second chart - but how?

 

Does anyone can lead me back on track?

 

Thanks for every hint!

 

Patrick

Need Help

13 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    You image does not show column headers, which one is the created date and time and which one is the printed timestamp?

     

    Can you show any additional information about your measures or other calculations that you have done?

    • Willborn's avatar
      Willborn
      Advocate III
      10 years ago

      Good morning Smoupre

       

      Attached the new Screenshot. What I did so far is merging 2 tables (using the quote number as key). The one table contains the Date when the Quote has been created (Created Date), the other one when it has been printed (Printed Date). Then I extracted the duration (Duration), and in a new column the duration in days (DurationDays). The Goal (Goal) is to deliver the quotes within 1 day to the customer. Then I created a new column for the legend (Legend) using a modified code snipped from this forum:

       

      Legend = if [#"DurationDays"] = null 
then "Unknown / not printed"
else if [#"DurationDays"] <= 1
then "1 day"
else if [#"DurationDays"] <= 3
then "1-3 days"
else if [#"DurationDays"] < 0
then ">3 days"
else ">3 days"

       

       

       

      I actually need to visualize

      - the Amount of the quotes in the last month (march) in % printed in 1 day/1-3 days/>3days

      - the deviation to the previous month (february) in %, such as +15% / -12% somehow in red and green color.

       

      I'll try to use the KPI visualization for that, but without sucess :o/

       

      Regards Patrick

      • Willborn's avatar
        Willborn
        Advocate III
        10 years ago

        Hi There

         

        I'm one step further. I actually tried to do that in Excel, that was pretty easy (static, using SUM). Also tried to figure out how to do the measure, but didnt got a valid DAX code togheter. Actually I have to calculate only the average of the last month against the month before. My goal is to have this value in the fields with the grey background.

         

         

        Guess the calculation should be as follows:

        Measure = ((Average "DurationDay" of February - Average "DurationDay" of March)/Average "DurationDay" of February)

        Whereas February and March shall be dynamic. Otherwise I have to manually filter the report each month...

        But how to do that in DAX?

         

        Actually I implemented a Date-Table, where I have all dates, years, quarter, month, weeks etc. in and connected this with the other tables. I dont know if this help me somehow in this topic, but I'll try furter...

         

        Regards Patrick