How to filter a measure on specific value in dax

[imjeed]

Hi All,

i need your support on my issue, firstly i am sorry if my english is not clear to you! ^_^

i have data that show my a sales for each day, so i have created a measure to compare a total sales for each day of current month with the previouse month like below:

Month	Day	Total sales in current month	total sales in previous month
Nov	1	1000	5200
Nov	2	2000	5000
Nov	3	5000	2500
Nov	4	1500	1000
Nov	5	3000	1500

what i did is correct as a full summary, but i will do the same measure for each company name,

when i filter a data for each company name it's show me a correct data of current sales, but for calculated sales of pervious month it' look like a fixed number for all companies, it's show me a total for all company..

Example:

company name	Month	Day	Total sales in current month	total sales in previous month
A	Nov	1	200	5200
B	Nov	1	300	5000
C	Nov	1	1000	2500
D	Nov	1	1200	1000

note that ( Total sales in current month ) is changed based on filter , but ( total sales in previous month ) is fixed for each company becuase my measure not consider that.

i want exactly to breakdown my calculation ( total sales in previous month ) for each company name to match a total sales of current month, find below my measure:

Measure =

CALCULATE (

SUM ( 'Sales'[Total Amounts] ),

FILTER (

ALL( 'Sales'),

MONTH ( 'Sales'[SalesDate] )

= MONTH ( MAX ( 'Sales'[SalesDate] ) ) - 1

&& DAY ( ''Sales'[SalesDate] ) = DAY ( MAX ( ''Sales'[SalesDate] ) )

)

)

hope my issue is clear to you, and hope to have a support from you guys.

Regards,

[v-yiruan-msft]

Hi  @imjeed ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @imjeed ,

Please update the formula of measure as below and check if it return the expected result...

Measure =
VAR _seldate =
    SELECTEDVALUE ( 'Sales'[SalesDate] )
VAR _pmedate =
    EOMONTH ( _seldate, -1 )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Total Amounts] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[company name] = _selcompany
                && YEAR ( 'Sales'[SalesDate] ) = YEAR ( _pmedate )
                && MONTH ( 'Sales'[SalesDate] ) = MONTH ( _pmedate )
                && DAY ( 'Sales'[SalesDate] )
                    = IF ( DAY ( _seldate ) > DAY ( _pmedate ), DAY ( _pmedate ), DAY ( _seldate ) )
        )
    )

If the above one can't help you get the desired result, please provide some sample data in your table ‘Sales’ (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@imjeed , You need to use date table. With help from date table try a measure like

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s