Forum Discussion

B_Albuquerque's avatar
B_Albuquerque
Frequent Visitor
8 years ago
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...
Need Help
  • CoreyP's avatar
    CoreyP
    8 years ago

    B_Albuquerque,

     

    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?

CoreyP's avatar
CoreyP
Icon for Solution Sage rankSolution Sage
8 years ago

Hi B_Albuquerque!

 

I'm not sure I fully understand your question. Have you tried creating a Date dimension table with a relationship to your fact table? Then you could have different fields in your date table that specific which time period is what...

B_Albuquerque's avatar
B_Albuquerque
Frequent Visitor
8 years ago

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's avatar
    CoreyP
    Icon for Solution Sage rankSolution Sage
    8 years ago

    B_Albuquerque,

     

    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?

    • B_Albuquerque's avatar
      B_Albuquerque
      Frequent Visitor
      8 years ago

      I will try that as soon as I can, thank you.