topic Re: Trend Analysis based on relative starting date in DAX Commands and Tips

Trend Analysis based on relative starting date

kdaya — Mon, 12 Aug 2019 23:22:24 GMT

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

Re: Trend Analysis based on relative starting date

Mariusz — Tue, 13 Aug 2019 08:43:06 GMT

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.

Re: Trend Analysis based on relative starting date

kdaya — Tue, 13 Aug 2019 13:00:12 GMT

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

Re: Trend Analysis based on relative starting date

Mariusz — Tue, 13 Aug 2019 13:41:21 GMT

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.

Re: Trend Analysis based on relative starting date

kdaya — Tue, 13 Aug 2019 14:03:55 GMT

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

)

Any clues on why this could be happening

Thanks in advance

Re: Trend Analysis based on relative starting date

Mariusz — Tue, 13 Aug 2019 14:11:49 GMT

Hi kdaya

Sorry, but what is the problem?

Best Regards,
Mariusz

Please feel free to connect with me.

Re: Trend Analysis based on relative starting date

kdaya — Tue, 13 Aug 2019 22:41:04 GMT

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.

Re: Trend Analysis based on relative starting date

Mariusz — Wed, 14 Aug 2019 07:23:21 GMT

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.