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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
imjeed
Frequent Visitor

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Users online (1,777)