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 want a measure to dynamically show the correct target revenue for a given time period so I can compare it to actuals. I am having trouble taking an annualized target revenue and breaking it up to a daily grain where a conditional is needed.
I have a simple data model of 2 fact tables that share a date dimension.
Fact Tables:
Net Revenues (Daily granularity)
Target Revenues (Annual grain)
Dimensions:
Date Table
I want to derive the target daily revenue from the annual number to show accurate YTD and Period to date comparisons to actuals.
The issue is that I only want to do this based on the TradingDays in the selected period. And I want the DAily Target to sum up to the Target for the year.
Here's the basic structure of the data.
Net Revenues are daily. Generally revenues are only generated on trading days. However, in some circumstances its possible they are not. So I need all dates and can't just filter the table down to just trading dates.
Targets as shown below are an annual number.
I want the daily target to divide the annual target by the number of trading days and be applied to trading days only. Then I want the Daily Target to sum up for the period correctly. Here is an example output.
Date | Net Revenues | Target | Act - DailyTar | Daily Target | IsTradingDay |
2021 | 200,000 | 252,000 | -32,000 | 252,000 | 252 |
Jan | 50,000 | 30,000 | 20,000 | 20 | |
1 | 0 | 0 | |||
2 | 500 | 500 | 0 | 0 | |
3 | 5000 | 4,000 | 1,000 | 1 | |
4 | 2000 | 1,000 | 1,000 | 1 |
I've successfully calculated the daily target. It is overstating the Annual Target when summed up, b/c it is showing the Annual/Trading Days but for all 365 days in the year.
To be clear in the above example it would sum up the 2022 DailyTarget to 365,000 as it applies the 1,000 to every day not just trading days.
The following IF statement fails:
"A single value for column 'isTradingDay' in the table 'DateTable' cannot be determined...'
Hi @tamerj1 ,
Yes the [isTradingDay] column is in the date column.
The above brought me a step closer, but the totals do not sum up correctly for the month or the year. I ended up needing to use the following. Which works only as long as my matrix has only Dates (Year, Month, Day) in the rows.
Please try
CustomPeriodTradingDayTargets =
VAR startP =
FIRSTDATE ( DateTable[Date] )
VAR endP =
LASTDATE ( DateTable[Date] )
VAR daysinP =
DATEDIFF ( startP, endP, DAY ) + 1
RETURN
SUMX (
SUMMARIZE ( DateTable, DateTable[Year], DateTable[Year Month] ),
CALCULATE (
SUMX (
DateTable,
IF ( DateTable[isTradingDay] = 1, [DailyTargets] * daysinP, 0 )
)
)
)
Hi @bschwantner
[isTradingDay] column is in which table?
Supposing that it is in the date table and that [DailyTargets] is a measure then you may try
CustomPeriodTradingDayTargets =
VAR startP =
FIRSTDATE ( DateTable[Date] )
VAR endP =
LASTDATE ( DateTable[Date] )
VAR daysinP =
DATEDIFF ( startP, endP, DAY ) + 1
RETURN
SUMX (
DateTable,
IF ( DateTable[isTradingDay] = 1, [DailyTargets] * daysinP, 0 )
)
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |