Forum Discussion

MarkCBB's avatar
MarkCBB
Icon for Helper V rankHelper V
7 years ago

MTD Calculations

Hi there,

 

I am the following measure that works perfectly.

PMTD UNIT SALES 2 = 
    CALCULATE([TOTAL UNITS],
        DATESMTD(
            DATEADD('CALENDAR'[Date],-1,MONTH)))

The above gives me the Total units sold for the previous month.

  • When this measure is used in a table with dates as rows. The result accumulates as expected. 
  • When this measure does not have dates as a filter context the result shows the full total for the previous month. (If if the current month is a partial month).

So when the above is used in a card I can see what the total sales were for last the previous month and this would be the target to beat this month. 

 

However, I also created this measure that will show the actual MTD total based on the latest date in the current month.

 so if the current month is the 13th of October, the Previous MTD would go up until the 13th of September.

PMTD UNIT SALES = 
CALCULATE([MTD UNIT SALES],
    DATEADD(
        LASTDATE('CALENDAR'[Date]),-1,MONTH))

The above measure also works perfectly, but in that is the problem, for Example, Feb has 28 days, and Jan has 31 days, in my report, I am showing Feb outperformed Jan even though Jan had higher sales, that is because I am only taking the first 28 days of Jan and at that point in Jan, Feb had higher sales. 

 

So my question is, is there a way of combining these 2 measures? I would like the current previous month to be measured with the above measure and the prior months of the previous month to use the 1st measure. 

3 Replies

  • Challenge 2, when I measure March against Feb, I am taking all 31 days of March against the max days of Feb (28). - WRT the 2nd Measure. I think this is more of a business related question now. Could anyone share some experience on this? 

  • Hi,

     

    I am not very clear about your question.  Please share some data and show the expected result.