Time intelligence Function

[yazenbarakat123]

Hello All! 

Im currently trying to write a Dax query to retrive the  "Current Date" for the previous month in a scheduling Report. 

Each Scheduled acticty has a Baseline date and a current date as seen below what I would like to do is  write a measure to return the "Current Date" from the previous month into a new Column called (Previous Month)   

 

Thanks for the Help in advance 🙂 

[yazenbarakat123]

You are the best !!! Thank you so much ❤️

[johnt75 · Accepted solution]

You could create a measure like

Previous Month =
LOOKUPVALUE (
    'Table'[Current Month],
    'Table'[Activity ID], SELECTEDVALUE ( 'Table'[Activity ID] ),
    'Table'[Reporting date], EOMONTH ( SELECTEDVALUE ( 'Table'[Reporting Month] ), -2 ) + 1,
    BLANK ()
)

[yazenbarakat123]

Thank you for the fast response @johnt75  it seems to be working but not to the exact level,  I still want to show other Activites if there isnt a Current date in the Previous month the measure seems to filter out the table 

 

[johnt75]

In the fields well, click on the down arrow next to Activity ID and choose "Show items with no data"

[yazenbarakat123]

One last question the first activity dosent capture the previous months current date for some reason 

 

[johnt75]

Try using Power Query to change the type of the reporting date column to date rather than datetime. Its possible that there are some microseconds, not being displayed, which means the two values don't match