DAX measure - ignore year and quarter slicers

[DarrenLau]

Hi,

Need some guidance to create measure(s) which can be broken down into two steps/parts.

Problem statement - need to calculate prior quarter's opportunities actual close amount by month based on the actual close dates.

Opportunities table has an active relationship on Estimated Close Date to Calendar table, and a secondary relationship on Actual Close Date.

Created Measures

Opps Actual Close = calculate(sum(Opportunities[Actual Revenue]),USERELATIONSHIP(Opportunities[Actual Close Date],'Calendar'[Date]))

The measure above works with the selected slicers (Year = 2020; Quarter = Q1).

I want to determine the actual close revenue by month for previous quarter, and the first step is to find out the 3 month values preceding the current quarter (in this example, 10,11,12; would change to 1,2,3 in 2020 Q2)

Second step is to use the month values to sum up the previous quarter's actual close revenue.

Will need 3 different measures as there are follow-on calculations that need to be done, eg:-

PrevQtrFirst Month

PrevQtrSecondMonth

PrevQtrThirdMonth

[amitchandak]

How you want to display. Like this one will year a qtr back month.

last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin