How to calculate PY MTD till yesterday

[Anonymous]

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

[Anonymous]

// 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

D

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

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.

[Anonymous]

@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 

[Greg_Deckler]

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

[Anonymous]

@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

[Anonymous]

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

[Anonymous]

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