cancel
Showing results for
Did you mean:
Regular Visitor

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

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 🙂

Highlighted
Super User I

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

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.

If this post has helped you, please give it a thumbs up!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!