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.
Hello,
PBI novice here looking for some help with a form of a DAX Running Totals calculation.
Background: I'm working with monthly revenue goals for my company's various markets and daily revenue values reported through our internal BI cube. I've divided these monthly revenue goals across all days in the month to match the granularity or the revenue data. I have two dimension tables - Market & Calendar. I have one fact table - Revenue - which has a date key, market key, a column for my revenue category (internal classification of revenue such as revenue, revenue pending payment from customers, sales pipeline, etc.), and a column with the actual revenue values in USD.
My Goal: I would like to create a running total of the revenue:goal shortfall/surplus to figure out how much revenue must be generated in the rest of the year to hit our yearly goal. Ideally this goal shortfall/surplus would be added to the year's remaining revenue goals. Please see the attached picture for my ideal.
Any idea how I can calculate the distributed remainder (New Monthly Goal) using DAX?
Please let me know what additional details may be needed to solve this.
Thank You
Edit for any future viewers: A similar answer to this topic can be found here
Solved! Go to Solution.
Hi @Fishy ,
As the screenshot you shared, We can create those simple measure to meet your need, If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Revenue Goal = IF ( ISINSCOPE ( Revenue[date key].[Month] ), 100, 1200 )
Difference = [Revenue Goal] - SUM ( Revenue[revenue value] )
New Monthly Goal = VAR selectmonth = SELECTEDVALUE ( Revenue[date key].[MonthNo] ) VAR thismonth = MONTH ( TODAY () ) VAR allDifference = CALCULATE ( [Difference], ALL ( Revenue ) ) VAR leftMonth = 12 - thismonth VAR goalmonth = INT ( allDifference / leftMonth ) VAR goalthis = allDifference - goalmonth * leftMonth RETURN IF ( ISINSCOPE ( Revenue[date key].[Month] ), SWITCH ( TRUE (), selectmonth > thismonth, goalmonth, selectmonth = thismonth, goalthis, "-" ), allDifference )
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hi @Fishy ,
As the screenshot you shared, We can create those simple measure to meet your need, If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Revenue Goal = IF ( ISINSCOPE ( Revenue[date key].[Month] ), 100, 1200 )
Difference = [Revenue Goal] - SUM ( Revenue[revenue value] )
New Monthly Goal = VAR selectmonth = SELECTEDVALUE ( Revenue[date key].[MonthNo] ) VAR thismonth = MONTH ( TODAY () ) VAR allDifference = CALCULATE ( [Difference], ALL ( Revenue ) ) VAR leftMonth = 12 - thismonth VAR goalmonth = INT ( allDifference / leftMonth ) VAR goalthis = allDifference - goalmonth * leftMonth RETURN IF ( ISINSCOPE ( Revenue[date key].[Month] ), SWITCH ( TRUE (), selectmonth > thismonth, goalmonth, selectmonth = thismonth, goalthis, "-" ), allDifference )
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Awsome, that worked as desired. Thank You!
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |