Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
imjeed
New Member

How to filter a measure on specific value in dax

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:

 

 

MonthDayTotal sales in current monthtotal sales in previous month
Nov110005200
Nov220005000
Nov350002500
Nov415001000
Nov530001500

 

 

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 nameMonthDayTotal sales in current monthtotal sales in previous month
ANov12005200
BNov13005000
CNov110002500
DNov112001000

 

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,
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.