Forum Discussion

yazenbarakat123's avatar
yazenbarakat123
Icon for Helper I rankHelper 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...
  • 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 ()
)
johnt75's avatar
johnt75
Icon for Super User rankSuper 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
Icon for Helper I rankHelper 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
    Icon for Super User rankSuper User
    4 years ago

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

    • yazenbarakat123's avatar
      yazenbarakat123
      Icon for Helper I rankHelper I
      4 years ago

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

       

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        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