Forum Discussion

bschwantner's avatar
bschwantner
Frequent Visitor
4 years ago

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

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.

 

DateNet RevenuesTargetAct - DailyTarDaily TargetIsTradingDay
2021200,000252,000-32,000252,000252
Jan50,000 30,00020,00020
1   00
2500 50000
35000 4,0001,0001
42000 1,0001,0001


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!

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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

     

  • bschwantner's avatar
    bschwantner
    Frequent Visitor
    4 years ago

    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. 
    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

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