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

[Willborn]

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

[Greg_Deckler]

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]

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]

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

[Willborn]

Update: It might help others - got the code for the Date table here: its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/

Thanks to the author for this great work!

[Sean]

@Willborn

You obviously need a COUNTing measure instead of SUM for these formulas but this should help you for the

MoM (Month on Month Calculations) MoM and MoM %

Let me know if you got it to work...

[Willborn]

Hi Sean

Didn't had success so far. I'll tried AVERAGE and SUM, but both gives me error message that only 1 Argument can be used.

The Date Column contains dates from April '16, March'16, Febraury '16 etc. - I'll need to calculate Delta in % of March and February.

So far I did

Measure = CALCULATE(1-(SUM('ZETA Order BCS BCAG'[DurationDays]) / (SUM('ZETA Order BCS BCAG'[DurationDays]))))

but I cant get out how to implement the two month into this formula...

Regards Patrick

[Sean]

@Willborn How do you calculate these? Do they represent different months?

[Willborn]

Hi Sean

From the one column where I have extracted the duration I did a simple count on the different values, according to the date filter i've setted (not very dynamic, but ok for the moment).

[Willborn]

Hi Sean

Could it be an option to:

1. Copy the Table in PBI Desktop

2. Remove the values except "Last Month" in table 1

3. Remove the Values except "Last Month-1" in table 2

But then > how to remove these lines, choosing  the March/February when loading the data in April?

[Sean]

You need COUNTing Measures for all categories in the Legend field (if I understand what you are trying to do)

You want to compare for - each Category in the Legend Column - Month vs previous Month  (for example)

1 day repsonses in March vs 1 day response in Februry ( # and %change)

1-3 day response in March vs 1-3 day responses in February

>3 day responses in March vs >3 day responses in February

So create COUNTROWS measures for all categories in the Legend Column

Then you can use these Measures in the formulas I gave you for MoM # and MoM %

[Sean]

@Willborn Visual and Page Level filters can only get you so far.

I've listed ALL Measures - you can obviosly combine some steps...

Let me know if this works.

[Willborn]

If I have the report level filter set to March '16 - does this also influence the metric? If this is the case, I will have just another problem...

[Willborn]

Finally tried with the following, but also here, the error is "too many arguments passed for the SUM function":

Measure = CALCULATE(1- (SUM ('ZETA Order BCS BCAG'[DurationDays];PREVIOUSMONTH('ZETA Order BCS BCAG'[Date])) / SUM('ZETA Order BCS BCAG'[DurationDays];PARALLELPERIOD('ZETA Order BCS BCAG'[Date];-1;month)) ))

When I have dates of April, the function "PREVIOUSMONTH" shall return the SUM of the values in [DurationDays] of the Month "March" within the Column [Date], and "PARALLELPERIOD" -1 this from February -  is this correct?

Regards from confused and screwed up Patrick