Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarkCBB
Helper V
Helper V

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 3
TomMartens
Super User
Super User

Hey,

 

here you will find what you are needing to tackle your callenges:

https://www.daxpatterns.com/time-patterns/

 

If not, create a pbix file with sample data, upload the data to onedrive or dropbox and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MarkCBB
Helper V
Helper V

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? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.