Previous 3 Months

DAX DAX Calculations
1

Hi Team,

Request for urgent help here. I need to figure out previous 3 months volume using DAX. For Example I am in March month so for last 3 month volume (January to March) I am using DAX = CALCULATE (
[Volume],
DATESINPERIOD ( ‘Calendar’[Date], LASTDATE ( ‘Calendar’[Date] ), -3, MONTH )
)
Now my selection month would be March only but I want to figure out volume from previous last 3 months (October to December) using DAX.

Please help me to calculate this, basically I need to analyze 3 month-over-3month volume comparison.

Thanks.
Regards
Harish Rathore

2

Hello @harishrathore,

Thank You for posting your query onto the Forum.

To achieve the results based on the condition that you’ve specified. You’ll be required to add one more Date table inside the data model and create an inactive relationship with the original Date table. Below is the screenshot of the data model provided for the reference -

Data Model
Data Model1761×875 45.9 KB

Now, create a measure for the “Total Sales Last 3 Months” -

Total Sales Last 3 Months = 
VAR _Reference_Date = 
MAX( Dates[Date] )

VAR _Previous_Dates = 
DATESINPERIOD(
    'Previous Dates'[Date] , 
    _Reference_Date , 
    - 3 ,
    MONTH )

VAR _Results = 
CALCULATE( 
    SUMX( Sales , 
        Sales[Order Quantity] * Sales[Unit Price] ) ,
            REMOVEFILTERS( Dates ) , 
            KEEPFILTERS( _Previous_Dates ) , 
            USERELATIONSHIP( Dates[Date] , 'Previous Dates'[Date] ) )

RETURN
_Results

Since based on the same slicer selection, you also want to calculate “Total Sales for Previous Last 3 Months”. Create the measure as provided below -

Total Sales Previous Last 3 Months = 
VAR _Reference_Date = 
MAX( Dates[Date] )

VAR _Previous_Dates = 
DATESINPERIOD(
    'Previous Dates'[Date] , 
    _Reference_Date , 
    - 6 ,
    MONTH )

VAR _Results = 
CALCULATE(
    CALCULATE( 
        SUMX( Sales , 
            Sales[Order Quantity] * Sales[Unit Price] ) ,
        REMOVEFILTERS( Dates ) , 
        KEEPFILTERS( _Previous_Dates ) , 
        USERELATIONSHIP( Dates[Date] , 'Previous Dates'[Date] ) ) , 
    DATEADD('Previous Dates'[Date] , -3 , MONTH ) )

RETURN
IF( ISBLANK( [Total Sales Last 3 Months] ) , 
    BLANK() , 
    _Results )

And finally, to fix the totals of the “Total Sales for Previous Last 3 Months” create this small measure -

Total Sales Previous Last 3 Months - Totals = 
SUMX(
    SUMMARIZE( 
        'Previous Dates' , 
        'Previous Dates'[Month & Year] , 
        "@Totals" , 
        [Total Sales Previous Last 3 Months] ) , 
    [@Totals] 
)

Below is the screenshot of the final results provided for the reference -

Final Results
Final Results1127×519 20.7 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Last 3 Months Vs Previous Last 3 Months - Harsh.pbix (702.6 KB)

2 Likes
3

Thanks for working on a solution on this post @harsh. Amazing job on how you come up with the solution.

Hello @harishrathore , did the response above solve your query?

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’

4

Hello @harishrathore,

Thank you for posting this query.

Hi @Harsh,
Thank you for the reply. Will also try this out as I have a question similar to this.

Thanks once again

5

I hope that the responses above were able to help you with your query

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Thanks a lot @Harsh for the solution. Kudos to you…

Regards
Harish Rathore