Forum Discussion
Solved
Calculating Values Between Snapshot Dates
Hello, I am fairly new to BI and have been trying to calculate values between historical snapshot dates (day to day). Ideally, I'd like the target data to be further summarized week-to-week, mont...
Hi Ayric2 ,
Please check:
Items Added = VAR MinDate_ = MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] ) VAR ThisDay_ = MAX ( 'Table'[Snapshot Date] ) VAR PreviousDay_ = CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ ) VAR ThisDayItems_ = DISTINCT ( 'Table'[Item ID] ) VAR PreviousDayItems_ = DISTINCT ( SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[Snapshot Date] = PreviousDay_ ), [Item ID] ) ) VAR SameItems_ = DISTINCT ( INTERSECT ( ThisDayItems_, PreviousDayItems_ ) ) RETURN IF ( MAX ( 'Table'[Snapshot Date] ) = MinDate_, "N/A", COUNTROWS ( ThisDayItems_ ) - COUNTROWS ( SameItems_ ) )Items Removed = VAR MinDate_ = MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] ) VAR ThisDay_ = MAX ( 'Table'[Snapshot Date] ) VAR PreviousDay_ = CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ ) VAR ThisDayItems_ = DISTINCT ( 'Table'[Item ID] ) VAR PreviousDayItems_ = DISTINCT ( SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[Snapshot Date] = PreviousDay_ ), [Item ID] ) ) VAR SameItems_ = DISTINCT ( INTERSECT ( ThisDayItems_, PreviousDayItems_ ) ) RETURN IF ( MAX ( 'Table'[Snapshot Date] ) = MinDate_, "N/A", COUNTROWS ( PreviousDayItems_ ) - COUNTROWS ( SameItems_ ) )Diff Count = VAR MinDate_ = MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] ) VAR ThisDay_ = MAX ( 'Table'[Snapshot Date] ) VAR PreviousDay_ = CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ ) VAR ThisDayCount_ = SUM ( 'Table'[Item Count] ) VAR PreviousDayCount_ = CALCULATE ( SUM ( 'Table'[Item Count] ), 'Table'[Snapshot Date] = PreviousDay_ ) RETURN IF ( MAX ( 'Table'[Snapshot Date] ) = MinDate_, "N/A", ThisDayCount_ - PreviousDayCount_ )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Ayric2 ,
Please check:
Items Added =
VAR MinDate_ =
MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] )
VAR ThisDay_ =
MAX ( 'Table'[Snapshot Date] )
VAR PreviousDay_ =
CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ )
VAR ThisDayItems_ =
DISTINCT ( 'Table'[Item ID] )
VAR PreviousDayItems_ =
DISTINCT (
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Snapshot Date] = PreviousDay_ ),
[Item ID]
)
)
VAR SameItems_ =
DISTINCT ( INTERSECT ( ThisDayItems_, PreviousDayItems_ ) )
RETURN
IF (
MAX ( 'Table'[Snapshot Date] ) = MinDate_,
"N/A",
COUNTROWS ( ThisDayItems_ ) - COUNTROWS ( SameItems_ )
)
Items Removed =
VAR MinDate_ =
MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] )
VAR ThisDay_ =
MAX ( 'Table'[Snapshot Date] )
VAR PreviousDay_ =
CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ )
VAR ThisDayItems_ =
DISTINCT ( 'Table'[Item ID] )
VAR PreviousDayItems_ =
DISTINCT (
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Snapshot Date] = PreviousDay_ ),
[Item ID]
)
)
VAR SameItems_ =
DISTINCT ( INTERSECT ( ThisDayItems_, PreviousDayItems_ ) )
RETURN
IF (
MAX ( 'Table'[Snapshot Date] ) = MinDate_,
"N/A",
COUNTROWS ( PreviousDayItems_ ) - COUNTROWS ( SameItems_ )
)Diff Count =
VAR MinDate_ =
MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] )
VAR ThisDay_ =
MAX ( 'Table'[Snapshot Date] )
VAR PreviousDay_ =
CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ )
VAR ThisDayCount_ =
SUM ( 'Table'[Item Count] )
VAR PreviousDayCount_ =
CALCULATE ( SUM ( 'Table'[Item Count] ), 'Table'[Snapshot Date] = PreviousDay_ )
RETURN
IF (
MAX ( 'Table'[Snapshot Date] ) = MinDate_,
"N/A",
ThisDayCount_ - PreviousDayCount_
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.