Calculate Previous year shows Blank

[priyanath1988]

Hi ,

why below code shows Blank for some "Calendar_LastDayOfMonth" value. 

Prior year =  DATEADD('Calendar'[Calendar_LastDayOfMonth], -1, YEAR)

How ever it works correct for values

Is it happening because my Calender table's value starts from 2017? but why it matters? it should calculate the value as expected.

Thanks

[Anonymous]

Please how do i fix the below error message when try to load data?

'Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The key didn't match any rows in the table.. '.

[priyanath1988]

@Anonymous , Could you please post your question in a new thread? Tis way you will get correct response sooner. My post is around the previous month/ year calculation.

Thanks.

Priya

[Ashish_Mathur]

Hi,

Yes, it is because your Calendar table starts from 2017.

[priyanath1988]

@Ashish_Mathur , Thanks for your response.

In that case why Blank is coming for Prior month for below example

Prior Month = DATEADD('Calendar'[Calendar_LastDayOfMonth], -1, MONTH)

   Thanks,

Priya

[Ashish_Mathur]

Does this measure work

=calculate(min('Calendar'[Calendar_LastDayOfMonth]),previousmonth('Calendar'[Calendar_LastDayOfMonth]))

[priyanath1988]

@Ashish_Mathur , yes this works. Thank you so much.

Could you please suggest solution for the previous year so that it does not show Blank?

Thanks,

Priya

[Ashish_Mathur · Accepted solution]

You are welcome.  Ensure that the calendar goes one year prior from the first date.  Replace previousmonth with previousyear. 

[Greg_Deckler]

@priyanath1988 You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, 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...

[priyanath1988]

@Greg_Deckler , Thank you for your response. I tried one of your codes but it throws an error. Could you please suggest whats wrong in it?

Thanks

Priya