Get First day last 2 month

[Anonymous]

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?

[amitchandak]

@Anonymous ,

Try like

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

[Anonymous]

@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 · Accepted solution]

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.

[Anonymous]

@Icey Thanks Icey, 

I will try it today.