Forum Discussion

kdaya's avatar
kdaya
Icon for Helper I rankHelper I
7 years ago

Trend Analysis based on relative starting date

Hi Community   Please can i get some assistance with below issue i am trying to resolve?   I need to build a trend graph which shows for example the first 360 days of sales activity of a customer...
Date and Time
DAX
DAX Query
Desktop
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

Hi kdaya 

 

You can create a column like below in your Calendar Date Dimension,

360 Bins = 
VAR _date = 'Calendar'[Date]
VAR _today = TODAY()
RETURN 
SWITCH(
    TRUE,
    _date >= _today, BLANK(),
    _date >= _today - 30, 30,
    _date >= _today - 60, 60,
    _date >= _today - 90, 90,
    _date >= _today - 120, 120,
    _date >= _today - 150, 150,
    _date >= _today - 180, 180,
    _date >= _today - 210, 210,
    _date >= _today - 240, 240,
    _date >= _today - 270, 270,
    _date >= _today - 300, 300,
    _date >= _today - 330, 330,
    _date >= _today - 360, 360,
    BLANK()
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



kdaya's avatar
kdaya
Icon for Helper I rankHelper I
7 years ago

Hi Mariusz,

 

Thanks for taking the time to reply,

 

I am still relatively new DAX are you able to explain this please:

 

Are these new columns that i should create:

VAR _date = 'Calendar'[Date]

VAR _today = TODAY

I appreciate your assistance

 

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi kdaya 

     

    The whole expression is one column, VAR is a variable, you use variables if you need to reuse something like I did with date column and TODAY() function.

     

    here is an article that can probably explain this in more detail.

    https://www.sqlbi.com/articles/variables-in-dax/

     

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    Mariusz Repczynski

     

     

    • kdaya's avatar
      kdaya
      Icon for Helper I rankHelper I
      7 years ago

      Thanks Mariusz

       

      I tried that and realised they were variables (thanks for the link). However i seem to be getting incorrect results, my formula is below:

       

      DAY360 = VAR CDate = request[Created Time].[Date] VAR _Today = TODAY()
      RETURN
      SWITCH
          (TRUE,
          CDate >= _Today, BLANK (),
          CDate >= _Today - 30, 30,
          CDate >= _Today - 60, 60,
          CDate >= _Today - 90, 90,
          CDate >= _Today - 120, 120,
          CDate >= _Today - 150, 150,
          CDate >= _Today - 180, 180,
          CDate >= _Today - 210, 210,
          CDate >= _Today - 240, 240,
          CDate >= _Today - 270, 270,
          CDate >= _Today - 300, 300,
          CDate >= _Today - 330, 330,
          CDate >= _Today - 360, 360,
          CDate >= _Today - 390, 390,
          CDate >= _Today - 420, 420,
          CDate >= _Today - 450, 450,
          BLANK()
      )
       
      Formula showing incorrect numbers
      Correct numbers for the month i.e. March-17 = 1 April=34 etc
       
      Any clues on why this could be happening
       
      Thanks in advance
      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi kdaya 

         

        Sorry, but what is the problem? 

         

        Best Regards,
        Mariusz

        Please feel free to connect with me.
        Mariusz Repczynski