Forum Discussion

yazenbarakat123's avatar
yazenbarakat123
Helper I
4 years ago
Solved

Time intelligence Function

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 šŸ™‚ 

 

  • johnt75's avatar
    johnt75
    4 years ago

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

6 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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's avatar
      yazenbarakat123
      Helper I
      4 years ago

      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's avatar
        johnt75
        Super User
        4 years ago

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