Running total graph, MTD vs Last year Month, with cut off line for MTD.

[Anonymous]

Dear all, 

I am looking for some graphing solution in PowerBI as following( example made in Webi) .

1. Yellow line - MTD running turnover per day of current month . with a cut off at yesterday. 

2.  Gray line - Laster year , Full month , Running Turnover per day of month. 

3. Blue line - Running sum of a constant number divided by total days in month. (it is basically a straight line, where it ends the value of this given constant)

I have tried many different suggestions, My pain points still are

1. my MTD running total line is not cutting off at date of yesterday.

2. to show all three lines in one table. 

My turnover table has "Calendar Day" info in text format e.g "20200201" . Same day could have many turnover records per market groups.

I have linked it with a  datetable  DateT, on DateT[Calendar Day] columns (also in text format).

I have created then a DateT[Date] as date-time format. DateT[DayNo] shows the the day number in month.

Data sample link 
I appreciate there could be a reply with a sample pbxi file , if possible. 

Thanks in advance! 

The code I tried for MTD running total is : 

 

[PaulDBrown]

@Anonymous 

You can share the file by uploading to  OneDrive, Dropbox, Google Drive or similar and sharing the link. Any of those an option?

[Anonymous]

Hi Paul,

thanks for looking at it, the following is a sample data set.

Share file Pbix 

I am trying to use 3 measures to derive the 3 lines showing in chart, however in vain.

[PaulDBrown]

@Anonymous 

Hi there,

This is what I've achieved so far:

I'm not too sure the running total for last year is what you are after (seeing the values displayed in this years MTD), but basically I have not changed the actual calculation in your original measure (only the date references)

I have re-worked your date table to include continuous dates covering the whole range of dates included in your model (it is recomended to do so...) and re-written some measures (I have included these in a new table called "TEST Measures" for easy reference).

Please check to see if this solves your issue (or come back if you need further help)

Here is the PBIX file:

Sample Data TEST 

Please note that in order to create the Date Table I had to export the turnover data table to re-import it. Therefore the source file in Power Query in this PBIX file relates to a file on my computer; you will have to reconnect this table to your own source file in Power Query.

[Anonymous]

Hi @PaulDBrown 

Thanks for the fast reply! 

Your solution worked on each of the 2 visuals seperately,

however my main intention is to show these 3 measure in one graph. .(like following.)

So that we could track the yellow line  (MTD turnover Current month) , with Gray line (Turn over same month Last year), as well as comparing it with  blue line (Planned Turnover this month.(basically =a constant integer / Number of days in curernt month , e.g 2080 / 29 for Feb)

by visual a running total picture , for all 3 measures , 

then we could easy monitor ,  if we are above or under plan / last year sales , day by day in current month.

That comparing can easily be done in Webi but It seems this is too hard to be implemented into PowerBI. (did not find any post in POWERBI forum that could help my request here 😞 )

My question is then, how will you best show them on visuals in powerBI? any suggestions? 

[PaulDBrown]

@Anonymous 

It can be done no problems in PBI. However, one thing you need to check is the calculations for both MTD and previous year measures: one delivers values in the hundreds or thousands while the other delivers values in the BILLIONS!! So plotting them in the same chart, for direct comparison purposes, is pretty meaningless. (I have a suspicion you mean to divide the previous year data by the same denominator you used in the MTD, but your original measure did not have this in the calculation - just a direct SUM)

BTW, the constant line you mention will be a flat horizontal line along all the days, correct?

[Anonymous]

Hi @PaulDBrown ,

It is great to hear from you shortly!

Yes , it is my mistake that I forgot to divide it by a million. After divding, it should all be at a level of thousands.

The constant value is a monthly plan I derive from another datasource.

In the graph, it should be shown as a line that creeping up.  (running total of a day by day portion)

For example, Feb has 29 days,  the constant value we derived  is 2900. Then we need to creep up 100  each day (29000 /29), and ends 2900 at the 29th day. 

I am capable of dering the total number of day in current month, however i could not figure out how I can put it into the same axis on the graph. 

[PaulDBrown · Accepted solution]

@Anonymous 

Here you go:

I've updated the file in the link I posted previously.

[Anonymous]

Amazing Staff @PaulDBrown  !

It will take me little time to digest , implement and  revert.

It is definately the solution !

One quick question, is it possible  to pull day number as the X-axis instead of Current month date?  That's a minor issue , if it can not be realized.

I have also a question , regarding the if (max ()..) clause,  since it is not related to this post, I will PM you seperately. Hope it is OK 🙂 

thanks a million for the great solution! 

[PaulDBrown]

@Anonymous 

I'm glad we worked it out eventually.

As regards your question about using day numbers instead of dates, it would require different measures for those in which I've used time intelligence functions (namely DATESMTD) - Time intelligence functions require a "date" filter context.

Happy to help!

Best,

[Anonymous]

Hi @PaulDBrown ,

Thanks ! I finally realized the visulization under your help.

There are still few interested open questions. I hope you could help when having time. 

1. what is the best way to add a constant value to the running total, before it starts? 

Say that beofre month start, we are hving some adjustments , e.g 100.

That means , the running sum of current month turnover started at 140.18  instead of 40.18 on first day, in the following example. I tried few ways, without an succeed 😞

Question 2:  I am very courios , How could you achieved 29, by function Days in Month = DAY(ENDOFMONTH(DateT[Date])).   I used the same function , it shows different value(1) to me.  Guess You must have some hidden tricks in the measure group 🙂 

Question 3:  if is it possible, I hope you could help do a coding without Time-Intelligience (DATESMTD ), only for one measure "MTD Turnover" .  Since time intelligeice are not allowed in Direct query mode, and our sales report could very possibly need a direct link. 

Thanks! 

All the best 🙂

[PaulDBrown]

@Anonymous 

To answer your questions:

1) to adjust +100, just include +100 at the end of the measure (you will however need to establish a filter for the period you wish to see, since the +100 will be applied for every date in the filter context) - for example for this month (cutting off at yesterday), it would be:

100 + Cumul Turnover (cutoff Yesterday) = 
VAR calc100 = [TEST CALC M100 MTD accumulative turnover] +100
RETURN
IF(MAX(DateT[DatenumbMonth]) = YEAR(TODAY()) *100 + MONTH(TODAY()) 
&& MAX(DateT[Date]) < TODAY(); 
calc100; BLANK())

3) There is no secret for the calculation of number of days in the month! 🙂

All you have to be aware of is that the function ENDOFMONTH requires a date context to return the "last date of month" for that date. In other words, if you place the measure in a table or visual with dates, it will return the last date of the month corresponding to the date in the filter context (by adding the function DAY, the measure returns the day number for that (last of month) date):

 It's just one way of "counting" the number of days in each month

If you don't have a date context (for example if you use a card visual), the ENDOFMONTH fuction is "lost" and delivers a default value of 1. So, if you wish to use it in a card, as you have done for example, you need to establish a date reference. For the current month it would be:

Days in Month (for card) = 
CALCULATE(DAY(ENDOFMONTH(DateT[Date])); 
        FILTER(datet;
         DateT[Date] = TODAY()))

3)

a) MTD Turnover (for current month) without Time Intelligence:

MTD Turnover (no TI) = CALCULATE([Total Turnover]/1000000;
                      FILTER(ALL(DateT);
                       DateT[Date] <= MAX(DateT[Date])
                           && DateT[DatenumbMonth] = YEAR(TODAY()) *100 + MONTH(TODAY())))

b) MTD Turnover (for current month) without Time Intelligence (cutoff yesterday):

MTD Turnover (no TI - cut off yesterday) = 
VAR calc = CALCULATE([Total Turnover]/1000000;
            FILTER(ALL(DateT);
            DateT[Date] <= MAX(DateT[Date])
            && DateT[DatenumbMonth] = YEAR(TODAY()) *100 + MONTH(TODAY())))
RETURN
IF(MAX(DateT[Date]) < TODAY(); calc; BLANK())

And all this gives you this:

I've updated the file in the link posted above with the new measures (in a table called "New Measures") for your reference.

EDIT: Apologies since I have just realised that the new measures I originally posted returned the gross turnover values! I have now corrected them (divided by 1000000) and updated the measures included above as well as the file itself.