DAX LASTDATE () - how can I fix my formulas

[karo]

Hi,

Could you help me identify why in some cases I am getting (Blank) for Last Snapshot -1? What is my mistake in DAX? 

Last Snapshot = LASTDATE('Snapshot Date'[Snapshot Date])

Last Snapshot -1 = DATEADD(LASTDATE('Snapshot Date'[Snapshot Date]),-1,MONTH)

Case OK:

Case NOT OK:

 

Regards,

Karo

[karo · Accepted solution]

Hi @amitchandak , @KNP , @Anonymous ,

Thank you for all your help. I have resolved my issue using:

Last Snapshot Date = LASTDATE('Snapshot Date'[Snapshot Date])

Last Snapshot Date - 1 = ENDOFMONTH(PREVIOUSMONTH(LASTDATE('Snapshot Date'[Snapshot Date])))

Regards,

Karo

[Anonymous]

Hi @karo ,

It's glad to hear that you have resolve the problem. And thanks for sharing the solution here. Could you please mark the helpful post as Answered since it has been resolved? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[Anonymous]

Hi  @karo ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[Anonymous]

Hi @karo ,

Please update the formula of measure [Last Snapshot -1] as below and check if you can get the correct result...

Last Snapshot -1 = EOMONTH([Last Snapshot],-1)

Best Regards

[amitchandak]

@karo , try like

Last Snapshot -1 = date(Year([Last Snapshot]), month([Last Snapshot])-1, day([Last Snapshot]) )

dateadd need continuous dates in the table

Date as Dateadd - Decoding Date and Calendar 5-5
https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power...

[karo]

@amitchandak Thank you for reply, but still this is not the solution. When I am trying DATE() then I am getting even worse results:

Last Snapshot = LASTDATE('Snapshot Date'[Snapshot Date])

Last Snapshot -1 = DATEADD(LASTDATE('Snapshot Date'[Snapshot Date]),-1,MONTH)

Last Snapshot -1 (2) = Date(Year([Last Snapshot]),MONTH([Last Snapshot])-1,DAY([Last Snapshot]))

[KNP]

I might be wrong but I think @amitchandak was trying to refer to the column, maybe?

LastSnapshotDate =
DATE ( 
    YEAR ( 'Snapshot Date'[Snapshot Date] ), 
    MONTH ( 'Snapshot Date'[Snapshot Date] ) - 1, 
    DAY ( 'Snapshot Date'[Snapshot Date] ) 
)