Forum Discussion
Solved
Cumulative Sum Comparison in Assymetrical Dates
Hello there. I need to make a comparison of cumulative sum for four semesters (see below) of users that are registered in the system. The problem is that these semesters are arbitrary, and are no...
So, I created a Date dimension table by using the following:
Modeling > New Table >
DateDim = CALENDAR( DATE( 2016, 9, 29 ) , DATE( 2018, 9, 22 ) )
With a custom column:
Semester = IF( AND( DateDim[Date] >= DATE( 2016, 9, 29 ) , DateDim[Date] <= DATE( 2017, 4, 2 ) ), "2017-S1" , IF( AND( DateDim[Date] >= DATE( 2017, 4, 3 ) , DateDim[Date] <= DATE( 2017, 9, 23 ) ), "2017-S2" , IF( AND( DateDim[Date] >= DATE( 2017, 9, 23 ) , DateDim[Date] <= DATE( 2018, 4, 2 ) ), "2018-S1" , IF( AND( DateDim[Date] >= DATE( 2018, 4, 4 ) , DateDim[Date] <= DATE( 2018, 9, 22 ) ), "2017-S2" , "Between Semesters" ))))Would something like this work?
Hello there, Corey.
I'm had a hard time to type the question, because it's not a trivial one. I have a dimension of date ('bdbol_new bol_lead'[DATE INSERT]), but this dimension is not effective to make a comparison between the semesters because it screw the chart due the assymetrical nature of them.
So, I need to create some kind of "index" to make this comparisons but I'm having a LOT ot trouble figuring out how.
CoreyP
Solution Sage
Solution Sage
So, I created a Date dimension table by using the following:
Modeling > New Table >
DateDim = CALENDAR( DATE( 2016, 9, 29 ) , DATE( 2018, 9, 22 ) )
With a custom column:
Semester =
IF(
AND( DateDim[Date] >= DATE( 2016, 9, 29 ) , DateDim[Date] <= DATE( 2017, 4, 2 ) ),
"2017-S1" ,
IF(
AND( DateDim[Date] >= DATE( 2017, 4, 3 ) , DateDim[Date] <= DATE( 2017, 9, 23 ) ),
"2017-S2" ,
IF(
AND( DateDim[Date] >= DATE( 2017, 9, 23 ) , DateDim[Date] <= DATE( 2018, 4, 2 ) ),
"2018-S1" ,
IF(
AND( DateDim[Date] >= DATE( 2018, 4, 4 ) , DateDim[Date] <= DATE( 2018, 9, 22 ) ),
"2017-S2" ,
"Between Semesters" ))))Would something like this work?
I will try that as soon as I can, thank you.