cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kdaya Regular Visitor
Regular Visitor

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

 

 

 

2019-08-13 08_37_01-Support Customer Reporting [Read-Only] - Power BI Desktop.pngcurrently what i have in Power BI

2019-08-12 13_08_39-Ticket Trend Excel.pngdesired result achived in Excel but need this in Power BI

2019-08-13 08_50_41-Book1 - Excel.pngData used in excel to achieve the correct trend graph

 

7 REPLIES 7
Super User
Super User

Re: Trend Analysis based on relative starting date

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

Re: Trend Analysis based on relative starting date

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

 

Super User
Super User

Re: Trend Analysis based on relative starting date

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

Re: Trend Analysis based on relative starting date

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()
)
 
2019-08-13 23_22_34-Support Customer Reporting-1 [Read-Only] - Power BI Desktop.pngFormula showing incorrect numbers
2019-08-13 23_28_30-Support Customer Reporting-1 [Read-Only] - Power BI Desktop.pngCorrect numbers for the month i.e. March-17 = 1 April=34 etc
 
Any clues on why this could be happening
 
Thanks in advance
Super User
Super User

Re: Trend Analysis based on relative starting date

Hi @kdaya 

 

Sorry, but what is the problem? 

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

kdaya Regular Visitor
Regular Visitor

Re: Trend Analysis based on relative starting date

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.

Super User
Super User

Re: Trend Analysis based on relative starting date

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 21 members 1,002 guests
Please welcome our newest community members: