topic Re: Time intelligence Function in DAX Commands and Tips

Time intelligence Function

yazenbarakat123 — Mon, 25 Jul 2022 14:29:11 GMT

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 🙂

Re: Time intelligence Function

johnt75 — Mon, 25 Jul 2022 15:13:33 GMT

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 () )

Re: Time intelligence Function

yazenbarakat123 — Mon, 25 Jul 2022 15:28:23 GMT

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

Re: Time intelligence Function

johnt75 — Mon, 25 Jul 2022 15:32:17 GMT

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

Re: Time intelligence Function

yazenbarakat123 — Mon, 25 Jul 2022 15:34:16 GMT

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

Re: Time intelligence Function

yazenbarakat123 — Mon, 25 Jul 2022 15:37:20 GMT

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

Re: Time intelligence Function

johnt75 — Mon, 25 Jul 2022 15:44:58 GMT

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