Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
Solved! Go to 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
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
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
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
85 | |
65 | |
51 | |
45 |
User | Count |
---|---|
217 | |
88 | |
81 | |
65 | |
56 |