topic Re: Measure applied to rows based on conditional With Totals Adding up. in DAX Commands and Tips

Measure applied to rows based on conditional With Totals Adding up.

bschwantner — Thu, 31 Mar 2022 17:26:44 GMT

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...'

CustomPeriodTradingDayTargets =

var startP=FIRSTDATE(DateTable[Date])

var endP=LASTDATE(DateTable[Date])

var daysinP=DATEDIFF(startP,endP,DAY)+1

return

IF(isTradingDay=1, [DailyTargets] * daysinP, 0)

Any help is appreciated!

Re: Measure applied to rows based on conditional With Totals Adding up.

tamerj1 — Fri, 01 Apr 2022 09:50:47 GMT

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 ) )

Re: Measure applied to rows based on conditional With Totals Adding up.

bschwantner — Fri, 08 Apr 2022 14:38:38 GMT

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.

DailyTarget_Total =

var __table = SUMMARIZE(DateTable,DateTable[Date],"__value", [CustomPeriodTradingDayTargets])

RETURN

IF( HASONEVALUE(DateTable[Date]) = TRUE(),[CustomPeriodTradingDayTargets],SUMX(__table,[__value]))

As soon as I introduce another dimension, it fails to sum up again. So for instance if I have the same matrix as above but under each day I split by the department, the individual department components don't add up to the daily total.

Re: Measure applied to rows based on conditional With Totals Adding up.

tamerj1 — Fri, 08 Apr 2022 15:29:27 GMT

bschwantner

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 ) ) ) )