Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX

I need a measure to return me the last date of a sale to a client on the last month of a reference date. So, if i've selected mar/23, i need a measure to return the last date of a sale in feb/23.

 

I was trying to use calculate Max[datesales column] and filtering by datesales column so I could use a timeline. But I cant work out how to make this work.

 

I need something that i can also use in power pivot.

Need Help
Tips and Tricks
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

    expected result measure: =
VAR _selectedmonthend =
    MAX ( 'Calendar'[Month-Year sort] )
RETURN
    MAXX (
        CALCULATETABLE (
            Sales,
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month-Year sort] < _selectedmonthend )
        ),
        Sales[Date]
    )

     

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

    expected result measure: =
VAR _selectedmonthend =
    MAX ( 'Calendar'[Month-Year sort] )
RETURN
    MAXX (
        CALCULATETABLE (
            Sales,
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month-Year sort] < _selectedmonthend )
        ),
        Sales[Date]
    )