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...
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
Icon for Community Champion rankCommunity 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 )
            )
        )
    )