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.
Hi,
I have a table that contains sales- and budget data, including transaction dates, for a country and down to a sales area. I also have a factory calendar that contains dates per country (menaing that since there are four countries, every date in the factory calendar is present four times).
I guess that the right approach to this is a DAX function, and I have looked at YTD and MTD, but they require a unique date in the factory calendar, as far as I understand.
How can I make a running total YTD that is grouped by Country, Sales area and Date ?
@Anonymous ,you can get cumulative like with date table
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(Sales[Sales Date])))
or
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Date] <=max(Sales[Date])))
at where you want a reset
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Date] <=max(Sales[Date])
sales[Country] =max(sales[Country]) && sales[Area] = max(sales[Area])))
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
Hello Amitchandak, tank you for your help. I must admit that I know very little about DAX and measures. Below is what I have entered in a measure. I get an error message saying 'Unexpected Expression' on the line that I have marked with red below.
Hi @Anonymous ,
Missing "&&" before the red expression, maybe you can try the following formula.
Cumm Sales =
CALCULATE (
SUM ( 'Daily Sales & Budget per market and Sales Area Step5'[DailyInv] ),
FILTER (
ALLSELECTED ( 'Daily Sales & Budget per market and Sales Area Step5' ),
'Daily Sales & Budget per market and Sales Area Step5'[Date]
<= MAX ( 'Daily Sales & Budget per market and Sales Area Step5'[Date] )
&& 'Daily Sales & Budget per market and Sales Area Step5'[Calendar]
= MAX ( 'Daily Sales & Budget per market and Sales Area Step5'[Calendar] )
&& 'Daily Sales & Budget per market and Sales Area Step5'[Distrikt]
= MAX ( 'Daily Sales & Budget per market and Sales Area Step5'[Distrikt] )
)
)
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry for not answering, but I was caught up in other stuff, and have not had the time to test your suggestion yet. I should have time to check it during next week, and I'll come back with an answer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
51 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |