Trend Analysis based on relative starting date

[kdaya]

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

[Mariusz]

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.

[kdaya]

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]

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.

[kdaya]

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]

Hi @kdaya 

Sorry, but what is the problem? 

Best Regards,
Mariusz

Please feel free to connect with me.

[kdaya]

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.

[Mariusz]

Hi @kdaya 

Can you send the screen shot of a table filtered to 30 days?

Best Regards,
Mariusz

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

Please feel free to connect with me.