So i am working on a scenario wherein i need to calculate the Previous MTD till current Date.
My MTD Formula =
IF(ISBLANK([TotalSales]),BLANK(),CALCULATE([TotalSales],DATESMTD(MasterCalendar[FullDate2])))
PMTD formula till current date = CALCULATE([MTD Sales 1],DATEADD(FILTER(DATESMTD(MasterCalendar[FullDate2]),MasterCalendar[FullDate2] <= [LatestDay]),-1,MONTH))
Here Latestday is the model’s refresh date which would be always one day prior to today.
When i use the above PMTD calculation it returns me entire previous months MTD value but when i replace the <= sign with = it gives me correct value for that particular date. It would be great if some one can help with the minor fix that needs to be for this DAX
Hi @Vishy, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Hi @ankit , you know what when i tried to replicate the issue in a dummy file which i created for your request (as i cannot share the original Pbix), it is showing me correct values with dummy data:)
@ankit - Thanks for the quick response , but i believe i was not able to express my question correctly so what i am looking for is Previous MTD value till today’s date i.e. consider today is 7 May , i want to display what was my mtd value previous month till 7th April. This i want to display in card visual as a KPI and get the variance and %. Your measures are working correctly for the scenario as per your understanding with respect to the problem. But i am looking for something called as Same time last month MTD value. Just putting the screen shot of the query what you have provided and highlighting what value i am looking for.
Hi @Vishy, just a friendly reminder, if your original question has been answered within the forum it is important to mark your thread as ‘solved’. If you have a follow question or concern related to this topic please start a new topic. More details can be found here - Asking Questions On The Enterprise DNA Support Forum.
If you are an EDNA member, suggest to go through below course to get In-Depth information on DAX and learn concepts like Measure Branching, DateAdd function etc.
Hi @Vishy, we’ve noticed that no response has been received from you since the 7th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
HI Ankit , Thanks for your help, however the measure given by you perfectly works fine when put in a table , but if you put that in card it gives blank. I had tried that earlier so i switched to
Var LatestDay = MAXX(Sheet1,Sheet1[Date])
If you are looking to get PMTD value based on just the latest month in Card, you can try something like below, however if you are looking for something else then please explain in detail with example in a PBIX/Snapshot etc.
