Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get First day last 2 month

Hi all,

 

I currently have the below DAX to get the First day of the last 2nd month. I mean, when i select 4-Nov-2020 this gives date as 1-Oct-2020 which is correct. 

 

last 2 months = EDATE ( EOMONTH ( SELECTEDVALUE(Query1[STARTDATE] ), 0 ), -2 ) + 1

 

But when i select a date 25-Sep-2020 this gives date as 31-Jul-2020 instead of 1-Aug-2020.

 

I know this behaviour is because we are adding 1 day at the end. But any idea how can i get 1-Aug-2020?

Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    Try to use DATESBETWEEN to create a measure like so:

    Measure =
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    ALL ( 'Table' ),
    DATESBETWEEN ( 'Table'[Date], [Last 2 month], [End of last month] )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,

     

    Try like

    EOMONTH ( SELECTEDVALUE(Query1[STARTDATE] ), -2 )+ 1

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak thanks Amit

       

      I have another question with respect to the same date. 

       

      I need to calculate the Avg(bill)for the period between these two measures
       
      Last 2 month = EOMONTH ( SELECTEDVALUE(Query1[STARTDATE] ), -2 )+ 1
      End of last month = EOMONTH (SELECTEDVALUE(Query1[STARTDATE]), 1 )
       
      I'm not able to pass these dates in the DATESINPERIOD function.
       
      Kindly help me.
       
       
       
       
      • Icey's avatar
        Icey
        Community Support
        5 years ago

        Hi Anonymous ,

         

        Try to use DATESBETWEEN to create a measure like so:

        Measure =
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    ALL ( 'Table' ),
    DATESBETWEEN ( 'Table'[Date], [Last 2 month], [End of last month] )
)

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.