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.
I try to forecast the sales revenue over time. I made some progress, but the graph shows an incorrect values. The graph shows a value of 10,99, but the total amount of January should be 340. In case of Sales ID I expect January 340 Februari.
I have the following tables:
The table Dates contains the following columns
The table Services contains the following columns
Calculations
Measure = CALCULATE(SUM(Services[RevenuePerBillingDay]); FILTER(Services; COUNTROWS( FILTER( VALUES(CalendarDate[Date]); Services[StartDate]<=CalendarDate[Date] && Services[EndDate]>=CalendarDate[Date]) )>0 ) )
BillDays = CALCULATE( COUNT(CalendarDate[Date]); DATESBETWEEN(CalendarDate[Date];Services[StartDate];Services[EndDate]))
RevenuePerBillingDay = Services[Revenue]/Services[BillDays]
Solved! Go to Solution.
Hi @larbond,
You can try to use below formula to get the summary daily payment:
Total = VAR current_id = MAX ( Records[ID] ) VAR current_date = MAX ( 'Calendar'[Date] ) VAR curretn_Pay = CALCULATE ( MIN ( Records[PerDay] ), FILTER ( ALL ( Records ), current_date IN CALENDAR ( [StartDate], [EndDate] ) && [ID] = MAX ( Records[ID] ) ) ) RETURN curretn_Pay * COUNT ( 'Calendar'[Date] )
Notice: my formula only works when you limit date range to specific year, if you not choose the specific year, it will return wrong result.
Regards,
Xiaoxin Sheng
Finnaly, it works with an example of SQLBI.
https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
Hi @larbond,
You can try to use below formula to get the summary daily payment:
Total = VAR current_id = MAX ( Records[ID] ) VAR current_date = MAX ( 'Calendar'[Date] ) VAR curretn_Pay = CALCULATE ( MIN ( Records[PerDay] ), FILTER ( ALL ( Records ), current_date IN CALENDAR ( [StartDate], [EndDate] ) && [ID] = MAX ( Records[ID] ) ) ) RETURN curretn_Pay * COUNT ( 'Calendar'[Date] )
Notice: my formula only works when you limit date range to specific year, if you not choose the specific year, it will return wrong result.
Regards,
Xiaoxin Sheng
Finnaly, it works with an example of SQLBI.
https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |