cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yuqi Regular Visitor
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 😞

Snap7.jpg

 

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 🙂 

Snap10.jpg

 

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
Super User I

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

@Yuqi 

 

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):

eomonth.JPG

 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:

New leasures.JPG

 

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!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors