cancel
Showing results 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.

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:

 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,
1 ACCEPTED SOLUTION
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.
3 REPLIES 3
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.
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.
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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors