topic Re: How to calculate PY MTD till yesterday in DAX Commands and Tips

How to calculate PY MTD till yesterday

Anonymous — Tue, 21 Apr 2020 10:22:50 GMT

Hi,

I am trying to create a column which need to generate PY MTD data till yesterday last year. I am using this formula :

Order $ PY MTD1 = CALCULATE(SUM(Orders[Order $ PY MTD]),SAMEPERIODLASTYEAR(Orders[Cal Day]))

Actually above formula generates data PY MTD data till today last year.

Can anyone help me on this please?

Thanks,

Nisar

Re: How to calculate PY MTD till yesterday

Greg_Deckler — Tue, 21 Apr 2020 12:09:53 GMT

Not clear. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: How to calculate PY MTD till yesterday

Anonymous — Tue, 21 Apr 2020 12:16:42 GMT

Hi Greg_Deckler ,

what i posted in my question was, i wanted to find last year MTD till yesterday's date which means today's date is 21/4/2020. I want to show orders of Previous year MTD till yesterday which is 1/4/2019 to 20/4/2019.

I Hope i was clear in this time.

Thanks,

Nisar

Re: How to calculate PY MTD till yesterday

Greg_Deckler — Tue, 21 Apr 2020 12:47:26 GMT

OK, I am not aware of a time intelligence function that will do that. There may be one but I have checked and I don't see anything. So, you will have to go with https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008. So your calculation will be something along the lines of:

Measure =

VAR __CurrentDate = TODAY()

VAR __MinDate = DATE(YEAR(__CurrentDate) - 1),MONTH(__CurrentDate),1)

VAR __MaxDate = __CurrentDate - 1

RETURN

SUMX(FILTER('Table',[Date]>=__MinDate && [Date] <= __MaxDate),[Column])

Re: How to calculate PY MTD till yesterday

Anonymous — Wed, 22 Apr 2020 08:12:00 GMT

// You have to have a correct model for this to work. // Calendar is your date table. // Orders is your fact table connected to Calendar // via Orders[Cal Day] and Calendar[Date]. Order $ PY MTD1 = CALCULATE ( [Total Amount], SAMEPERIODLASTYEAR( DATESMTD( PREVIOUSDAY( LASTDATE(Calendar[Date] ) ) ) ) )

Best

Re: How to calculate PY MTD till yesterday

Anonymous — Wed, 22 Apr 2020 10:20:51 GMT

Thanks for reply Greg_Deckler . Tried implementing your Dax but the answer doesnt match when i verify it. Kindly find the formula below

Order $ PY MTD2 =

VAR __CurrentDate = TODAY()

VAR __MinDate = DATE(YEAR(__CurrentDate) - 1,MONTH(__CurrentDate),1)

VAR __MaxDate = __CurrentDate - 1

RETURN

SUMX(FILTER(Orders,Orders[Cal Day]>=__MinDate && Orders[Cal Day] <= __MaxDate),Orders[Net Order $])

Regards,

Nisar

Re: How to calculate PY MTD till yesterday

Anonymous — Wed, 22 Apr 2020 10:22:19 GMT

Thanks for your reply Anonymous . Tried implementing the Dax which you shared me. I got blank as a result.

Re: How to calculate PY MTD till yesterday

Greg_Deckler — Wed, 22 Apr 2020 13:36:54 GMT

Extremely difficult to troubleshoot without sample data and expected result. Your formula there seems to get a MinDate of the first of the month last year, so if today is 4/22/2020 then it would return 4/1/2020. Is that what you want? MaxDate in your case would be 4/21/2020, is that what you want?

You can verify that the individual components are what you want by just returning them in your RETURN statement and commenting out the SUMX with // in front of it.

Re: How to calculate PY MTD till yesterday

Anonymous — Wed, 22 Apr 2020 14:16:31 GMT

Greg_Deckler alright let me add screenshot for clear illustration.

In above picture, as you can see order $ PY MTD column shows data for previous year same day which is 22.April.2019. Actually i wanted to show data till 21.April.2019 which is previous year previous day.

Kindly let me know if there is any other information required.

Thanks

Re: How to calculate PY MTD till yesterday

Greg_Deckler — Wed, 22 Apr 2020 16:00:03 GMT

OK, perhaps:

VAR __CurrentDate = TODAY()

VAR __PreviousYearPrevious = DATE(YEAR(__CurrentDate) - 1,MONTH(__CurrentDate),DAY(__CurrentDate) - 1)

VAR __CurrentYearPreviousDay = __CurrentDate - 1

RETURN

SUMX(FILTER(Orders,Orders[Cal Day]>=__PreviousYearPrevious && Orders[Cal Day] <= __CurrentYearPreviousDay ),Orders[Net Order $])

Re: How to calculate PY MTD till yesterday

Anonymous — Wed, 22 Apr 2020 16:45:36 GMT

Greg_Deckler Thanks for your reply. Still the number are not showing correctly. I have asked backend team to create measure in SQL database itself. Appreciate your help.

Thanks,

Nisar

Re: How to calculate PY MTD till yesterday

Anonymous — Thu, 23 Apr 2020 14:39:07 GMT

You have my measure that works perfectly correctly but you are not doing it right. At the head of my measure is a hint:

// You have to have a correct model for this to work.
// Calendar is your date table.
// Orders is your fact table connected to Calendar
// via Orders[Cal Day] and Calendar[Date].

Best
D

Re: How to calculate PY MTD till yesterday

Anonymous — Thu, 23 Apr 2020 14:57:45 GMT

Hi Anonymous

I have used your dax to implement in mine.

Order $ PY MTD1 =

CALCULATE (

SUM(Orders[Net Order $]),

SAMEPERIODLASTYEAR(

DATESMTD(

PREVIOUSDAY(

LASTDATE('Date'[Date])

)

This time i have created a new date table and created a model. Still the numbers are not matching.

I don't have much tables here in my report, its just 2 tables. One is billing and another is date. Not sure whats causing this issue.

Meanwhile i have rectified it by talking with datawarehouse team. Still wanted to know how we can fix this in front end.

Anyway thanks for your help.

Thanks,

Nisar