Calculate No of customers for last 3 to 6 months based on selection

[Anonymous]

Hi Experts,

I want to calculate the [No of Customer] for dates between the last 3rd month to last 6th-month revenue.
to accomplish this requirement I've been tried below DAX with no success please help. 

this should be dynamic based on selected month year from the slicer.

Thanks. 

[Anonymous · Accepted solution]

Rev 3-6 month = CALCULATE ( [Total Revenues], 

DATESINPERIOD ( DimDate[Date], DATE(YEAR(LASTDATE( DimDate[Date] )), MONTH(LASTDATE ( DimDate[Date] ))-3, DAY(LASTDATE ( DimDate[Date] ))), -3, MONTH )  )

[amitchandak]

@Anonymous , Try something like this with date calendar

Rolling 3 till last 3 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-3,month)),-3,MONTH))

Rolling 3 till last 6 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-6,month)),-3,MONTH))

[parry2k]

@Anonymous you should use date dimension in the first expression of DATESBETWEEN instead of using fact table? and you don't need to use ALL function. Seems like you already have dimension, make sure it is marked as date table.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡