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.
For each service sale that will be delivered to customers over the contract period I want to evenly distrubute the total sale over the working days from the startdate to the end date. I have a Calendar table that defines all dates, and a Sales table that includes the Revenue, Start Date, End Date. I added a computed column BillingDays for the number of days excluding holidays and weekends. I added a computed column to set the revenue for each billing day. I also want a Measure that can be used for the displaying the revenue over the course of the contract from Start Date through End Date.
BillingDays = CALCULATE(COUNT('Calendar'[WorkDay]), DATESBETWEEN('Calendar'[CalendarDate], 'Sales'[StartDate], 'Sales'[EndDate] ), 'Calendar'[WorkDay] = 1 )
RevenuePerBillingDay = DIVIDE(Sales[Revenue],Sales[BillingDays],0)
Measure = CALCULATE(
SUM(Sales[RevenuePerBillingDay]),
FILTER ('Calendar','Calendar'[WorkDay]=1),
FILTER ('Sales','Sales'[StartDate] <= CALCULATE ( MAX ( 'Calendar'[CalendarDate] ) ) ),
FILTER ( 'Sales', 'Sales'[EndDate] >= CALCULATE ( MIN ( 'Calendar'[CalendarDate] ) ) )
)
A few problems:
Thank you for assistance.
The Key issue is to see that when WorkDay=0, the Measure for that day is $0.
Would anyone have ideas on how to achieve that?
Can you add another measure called Measure1 and use that in the graph:
Measure1 = if(WorkDays = 0, BLANK(), MEASURE)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Similar issue with trying to calculate this way:
SUM(Sales[RevenuePerBillingDay])*'Calendar'[WorkDay]
Results in the following error message:
A single value for column 'WorkDay' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
In your scenario, you have created the "BillingDays" and "RevenuePerBillingDay" based on the CalendarDate table. Now you need to limit the Sales table context if the CalendarDate date values is in date range of the Sales row context.
I assume you have Sales table below:
Then you should filter the CalendarDate date values to determine if rows in Sales table need to be included. The formula of the measure should be like below:
Measure = CALCULATE(SUM(Sales[RevenuePerBillingDay]), FILTER(Sales, COUNTROWS( FILTER( VALUES(CalendarDate[Date]), Sales[StartDate]<=CalendarDate[Date] && Sales[EndDate]>=CalendarDate[Date]) )>0 ) )
And if a date appears in multiple date ranges, the corresponding "RevenuePerBillingDay" will aggregate.
Regards,
Thank you for looking at this.
The results of the DAX expression you provided are the same as what I have had, but, I will dig in a bit more on the structure of what you suggested to understand the nesting methods on how you filtered.
The problem remanins that the Value of the Measure should be $0 in your example when IsWorkDay=0.
If it was SQL, it would be something like SUM(Sales.RevenuePerDay)*CalendarDate.IsWorkDay, but that kind of expression I cannot get to work in DAX.
In this scenario, we can build a calculated table to build that measure into a column. Then add a calculated column and apply condition with IF statement.
Table = ADDCOLUMNS(CalendarDate,"RevenuePerDay",[Measure])
RevenuePerBillingDay = IF('Table'[IsWorkDay]=1,'Table'[RevenuePerDay],BLANK())
Regards,
Your input was helpful, and based on this I have come up with the following (since I need to be able to slice by SalesPipelineID as well. I need to rename my table 'Calendar' to aviod any confusion with the Calendar funcion.
BillingForecast =
ADDCOLUMNS( CROSSJOIN(
SUMMARIZE('Sales', [SalesPipelineID]) ,
SUMMARIZE('Calendar',[CalendarDate],[WorkDay])
),
"RevenuePerDay",[Measure]*[WorkDay]
)
I am going to do a bit more testing with some scenarios to see if this is the desired result for all cases.
Is there a benefit to using Blank() instead of 0 for days that should not count revenue?
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 |
---|---|
105 | |
97 | |
80 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |