Time Intelligence Help - Previous MTD till Current Date

Hi All,

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

screen shot attached

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.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please share PBIX file to look further…

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

Hi @Vishy

Below measures are working for me on my data for PMTD. Please check if any is helpful.

PMTD formula till current date1 =
[MTD Sales 1] + OPENINGBALANCEMONTH ( [MTD Sales 1], Dates[Date] ) 

PMTD formula till current date = 
CALCULATE (
    [Total Sales],
    DATESBETWEEN (
        Dates[Date],
        DATEADD ( STARTOFMONTH ( Dates[Date] ), -1, MONTH ),
        MAX ( Dates[Date] )
    )
)

Thanks
Ankit Jain

@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.

Test MTD.pbix (79.6 KB)

Have shared the pbix with sample data

Note my original post measure which is

Previous MTD =

Var LatestDay = MAXX(Sheet1,Sheet1[Date])

Return

CALCULATE(SUM(Sheet1[Sales]),DATEADD(FILTER(DATESMTD(DimDate[Date]),DimDate[Date] <= LatestDay),-1,MONTH))

This works correctly for me but it fails in my actual production data. So it would be great if you have something different which might help me.

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.

Hi @Vishy

Based on the requirement, below shall work.

Prev_MTD_Ankit =
VAR LatestDay =
    MAXX ( Sheet1, Sheet1[Date] )
RETURN
    IF (
        MAX ( DimDate[Date] ) > LatestDay,
        BLANK (),
        CALCULATE ( [Sales MTD], DATEADD ( DimDate[Date], -1, MONTH ) )
    )

Hi @Vishy

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.

Thanks
Ankit J

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

Return

CALCULATE([Sales MTD],DATEADD(FILTER(DATESMTD(DimDate[Date]),DimDate[Date] <= LatestDay),-1,MONTH))

which gives me correct value as expected

Hi @Vishy

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.

Prev_MTD_Ankit =
VAR LatestDay =
    MAXX ( Sheet1, Sheet1[Date] )
RETURN
    CALCULATE (
        [Sales MTD],
        DATEADD (
            DATESBETWEEN ( DimDate[Date], MIN ( DimDate[Date] ), LatestDay ),
            -1,
            MONTH
        )
    )

EDNA_Solution_Test MTD.pbix (88.3 KB)
Thanks
Ankit Jain

Thanks for this alternate approch buddy much appreciated