Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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
Hi,
I am not very clear about your question. Please share some data and show the expected result.
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |