Forum Discussion

gauravjangra09's avatar
gauravjangra09
New Member
11 months ago
Solved

Power BI Measure Issue: Month-2 Sales Not Showing When Filtering

I have a Sales column where I calculate the sum of sales for the currently selected month. In addition, I also need to calculate the sum of sales for two months earlier.

The calculation works fine when all dates are in context. However, as soon as I filter the report to a single month, the measure only shows sales for the selected month and does not return the value for “month minus two.”

I tried addressing this using a disconnected calendar table, but ran into issues:

  • If the table is disconnected → it doesn’t filter the sales data properly.

  • If the table is connected → it behaves the same as the main calendar and still doesn’t solve the issue.

Also, in my calculation, I don’t just want the result for the current month — I want to be able to see the historical data as well (e.g., if I select multiple months, it should show sales for each of those months along with their respective “month minus two” values).

Need Help
  • mdaatifraza5556's avatar
    mdaatifraza5556
    11 months ago

    Hi gauravjangra09 

    Can you please try the below dax 

    Ensure the date table is mark as date table


    Sales Previous 2 Months =
    CALCULATE (
        [Total Sales],
        DATESINPERIOD (
            'date'[Date],
            EOMONTH ( MAX ( 'date'[Date] ), -1 ),
            -2,
            MONTH
        )
    )

     

    If this answers your questions, kindly accept it as a solution and gvie kudos.
  • Anonymous's avatar
    Anonymous
    11 months ago

    Hi gauravjangra09 ,

    Thanks mdaatifraza5556  your approach is correct, but it needs to clear the month filter first, otherwise it won’t return values when only one month is selected.

    Use this version.

    Sales Previous 2 Months =
    VAR Anchor = MAX( 'Date'[Date] )
    VAR StartPrev2 = DATE( YEAR( EDATE( Anchor, -2 ) ), MONTH( EDATE( Anchor, -2 ) ), 1 )
    VAR EndPrev1 = EOMONTH( Anchor, -1 )
    RETURN
    CALCULATE([Total Sales],FILTER(ALL( 'Date' ),'Date'[Date] >= StartPrev2 && 'Date'[Date] <= EndPrev1))


    Thanks,
    Akhil. 

5 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Icon for Super User rankSuper User
    11 months ago

    Hi gauravjangra09 

    Can you please try the below dax 

    Ensure the date table is mark as date table


    Sales Previous 2 Months =
    CALCULATE (
        [Total Sales],
        DATESINPERIOD (
            'date'[Date],
            EOMONTH ( MAX ( 'date'[Date] ), -1 ),
            -2,
            MONTH
        )
    )

     

    If this answers your questions, kindly accept it as a solution and gvie kudos.
  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi gauravjangra09 ,

    Thanks mdaatifraza5556  your approach is correct, but it needs to clear the month filter first, otherwise it won’t return values when only one month is selected.

    Use this version.

    Sales Previous 2 Months =
    VAR Anchor = MAX( 'Date'[Date] )
    VAR StartPrev2 = DATE( YEAR( EDATE( Anchor, -2 ) ), MONTH( EDATE( Anchor, -2 ) ), 1 )
    VAR EndPrev1 = EOMONTH( Anchor, -1 )
    RETURN
    CALCULATE([Total Sales],FILTER(ALL( 'Date' ),'Date'[Date] >= StartPrev2 && 'Date'[Date] <= EndPrev1))


    Thanks,
    Akhil. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi gauravjangra09 ,

    Just checking in  were you able to try the updated measure with the cleared month filter? It should now correctly return values for the previous 2 months even when only one month is selected. Let us know if it works as expected or if you’re still seeing issues.

    Thanks,
    Akhil.

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi gauravjangra09 ,

    Just wanted to circle back were you able to test the updated measure? If it’s still not giving the expected result, could you share a sample of the output you’re seeing so we can troubleshoot further?

    Thanks,
    Akhil.

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi gauravjangra09 ,

    I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

    Thanks,
    Akhil.