Forum Discussion
Graph Values and Zero-Values Only Within Date Range
- Anonymous7 years ago
@@Evogelpoh
The “+0” will make your measure always return a value. This was you’ll never get blanks. What I think you want is to maintain a blank value if -there aren’t any sales before the last date in the period you’re analyzing, nor sales after.
With a setup like:
If you create this measure:
Sale Amount = VAR SaleAmountInPeriod = SUM ( Sales[Sale] ) VAR LastDateInPeriod = MAX ( 'Calendar'[DateTime] ) VAR ExistLaterSales = NOT ( ISEMPTY ( FILTER ( ALL ( Sales ); Sales[DateTime] > LastDateInPeriod ) ) ) VAR ExistEarlierSales = NOT ( ISEMPTY ( FILTER ( ALL ( Sales ); Sales[DateTime] < LastDateInPeriod ) ) ) RETURN IF ( NOT ( ISBLANK ( SaleAmountInPeriod ) ); SaleAmountInPeriod; IF ( AND ( ExistLaterSales; ExistEarlierSales ); 0 ) )And, if you have Sales entries like:
Sale table with hourly records
With out any filtering, you'll get this when you create the graph:Pointing at First recordPointing at last record
If you than apply a filter to only show November, you get this:
@@Evogelpoh
The “+0” will make your measure always return a value. This was you’ll never get blanks. What I think you want is to maintain a blank value if -there aren’t any sales before the last date in the period you’re analyzing, nor sales after.
With a setup like:
If you create this measure:
Sale Amount =
VAR SaleAmountInPeriod =
SUM ( Sales[Sale] )
VAR LastDateInPeriod =
MAX ( 'Calendar'[DateTime] )
VAR ExistLaterSales =
NOT ( ISEMPTY ( FILTER ( ALL ( Sales ); Sales[DateTime] > LastDateInPeriod ) ) )
VAR ExistEarlierSales =
NOT ( ISEMPTY ( FILTER ( ALL ( Sales ); Sales[DateTime] < LastDateInPeriod ) ) )
RETURN
IF (
NOT ( ISBLANK ( SaleAmountInPeriod ) );
SaleAmountInPeriod;
IF ( AND ( ExistLaterSales; ExistEarlierSales ); 0 )
)
And, if you have Sales entries like:
Sale table with hourly records
With out any filtering, you'll get this when you create the graph:
Pointing at First recordPointing at last record
If you than apply a filter to only show November, you get this:
- Anonymous3 years agoNot applicable
Great Solution, It is working perfectly. Thank you