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 customers split into bins of 30 days. 

 

The difficulty i am facing is that customers dont have the same sales start date. Therefore, i have on customer starting in Jan-2016 and another in March-2016 etc...

 

 

 

currently what i have in Power BI

desired result achived in Excel but need this in Power BI

Data used in excel to achieve the correct trend graph

 

Date and Time
DAX
DAX Query
Desktop

7 Replies

  • 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