Hello,
So I have an issue where a target field is populated before the sales data arrives at the start of the month. This is skewing my results. In a table this isn't too much of a problem because you can just filter out the row using 'is not blank' tick box and it will re-calculate the data.
| Sales MTD | Budget MTD | Variance | Variance% |
| 70 | 90 | -20 | -22.2 |
| 50 | 70 | -20 | -28.6 |
| 25 | 60 | -35 | -58.3 |
| | 55 | -55 | -100.0 |
| 145 | 275 | -130 | -47.27 |
My question is about the measures which I drop into cards. How do you stop it counting the blank rows? If the Sales MTD column/row is blank, I don't want it to add in values from the budget or variance columns into my measures either. This is my DAX:
Sales MTD:
CALCULATE(TOTALMTD([SQLSales],DateDimension[Date]),DateDimension[Date]<=TODAY())
Budget MTD: [Target MTD]*(DIVIDE([Workdays Passed CM],[Work Days CM],0))
Variance%MTD =
VAR __BASELINE_VALUE = [Budget MTD Calc]
VAR __VALUE_TO_COMPARE = [SQLSalesMTD]
RETURN
IF(
NOT ISBLANK(__VALUE_TO_COMPARE),
DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
)
VarianceSQLMTD = ([SQLSalesMTD]-[Budget MTD Calc])
Any ideas would be really welcome. Thank you.
