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
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

       

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

        Hi Mariusz 

        for the first 30 days it should be about 35 sold items but when i use that formula it giving me 9 only.