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

Analyzing Sales Data line items

Hi there,

I have sales data line items for the last 3 years that looks like the below example, and I would need some help with analyzing this data:

____Period____|____Product____|____Customer____|____Volume____|
Apr 2019Product 1Customer A100
May 2019Product 1Customer A150
Jun 2019Product 1Customer A170
Jul 2019 Product 1Customer A160
Aug 2019Product 1Customer A170
Apr 2020Product 1Customer A110
May 2020Product 1Customer A160
Jun 2020Product 1Customer A180
Jul 2020Product 1Customer A170

 

My questions are the following:

  1. The company has a custom calendar, e.g Period 1 is July, Period 2 is August, etc. How can I calculate the year-to-date volume this year and compare it with the year-to-date volume last year? Also, this year-to-date calculation need to be updated based on which month is the most recent full month data (e.g in November, year-to-date would mean the period between July and October)
  2. I need to calculate rolling trends based on a selection in a slicer: e.g if the user selects July, then I need to summarize the rolling 3 months this year (May, June, July) and compare the growth with the rolling 3 months last year. But if the user updates the slicer to February then the calculation also refreshes to capture the last three months (December, January and February).

Thanks,

kukszi

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Analyzing Sales Data line items

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Analyzing Sales Data line items

Hey @kukszi ,

 

start by reading this article, it contains almost everything one needs to know about date-related calculations:
https://www.daxpatterns.com/time-patterns/

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Super User V
Super User V

Re: Analyzing Sales Data line items

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Super User IX
Super User IX

Re: Analyzing Sales Data line items

@kukszi , find a calendar of your choice -

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

YTD can take year-end date. Your QTR is will work with time intelligence

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

 

rolling

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))  
Rolling 3= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],staroffmonth(Sales[Sales Date]),3,MONTH))
Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))
Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-13,MONTH))
Rolling 3 till last 3 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-3,month)),-3,MONTH))

refer for more deatils

Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD Questions — Time Intelligence 2–5
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Microsoft
Microsoft

Re: Analyzing Sales Data line items

Hi @kukszi 

I build a table like yours to have a test.

1.png

I build measure to achieve your goals.

  1.  
Year-to-date volume this year =

VAR _Selected = SELECTEDVALUE(Calender[Date].[MonthNo])

VAR _MAXMon =

    MAXX (

        FILTER ( ALL ( 'Table' ), 'Table'[__Period____|].[Year] = YEAR ( TODAY () ) ),

        'Table'[__Period____|].[MonthNo]

    )

RETURN

    CALCULATE (

        SUM ( 'Table'[____Volume____|] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[__Period____|].[Year] = YEAR ( TODAY () )

                && 'Table'[__Period____|].[MonthNo] < _MAXMon

                && 'Table'[__Period____|].[MonthNo] >= _Selected

        )

    )
Year-to-date volume last year =

VAR _Selected = SELECTEDVALUE(Calender[Date].[MonthNo])

VAR _MAXMon =

    MAXX (

        FILTER ( ALL ( 'Table' ), 'Table'[__Period____|].[Year] = YEAR ( TODAY () ) ),

        'Table'[__Period____|].[MonthNo]

    )

RETURN

CALCULATE (

        SUM ( 'Table'[____Volume____|] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[__Period____|].[Year]

                = YEAR ( TODAY () ) - 1

                && 'Table'[__Period____|].[MonthNo] < _MAXMon

                && 'Table'[__Period____|].[MonthNo] >= _Selected

        )

    )

When My latest value is in Nov 2020,Result:

Default:

2.png

Select July:

3.png

2.

Rolling 3 months last year =

VAR _selectedmonth =

    SELECTEDVALUE ( Calender[Date].[MonthNo] )

VAR _a =

    CALCULATE (

        SUM ( 'Table'[____Volume____|] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[__Period____|].[Year]

                = YEAR ( TODAY () ) - 1

                && 'Table'[__Period____|].[MonthNo] <= _selectedmonth

                && 'Table'[__Period____|].[MonthNo] >= _selectedmonth - 2

        )

    )

VAR _b =

    CALCULATE (

        SUM ( 'Table'[____Volume____|] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[__Period____|].[Year]

                = YEAR ( TODAY () ) - 2

                && 'Table'[__Period____|].[MonthNo] <= 12

                && 'Table'[__Period____|].[MonthNo] > 12 - 3 + _selectedmonth

        )

    )

RETURN

    IF ( _selectedmonth <= 2, _a + _b, _a )
Rolling 3 months this year =

VAR _selectedmonth =

    SELECTEDVALUE ( Calender[Date].[MonthNo] )

VAR _a =

    CALCULATE (

        SUM ( 'Table'[____Volume____|] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[__Period____|].[Year] = YEAR ( TODAY () )

                && 'Table'[__Period____|].[MonthNo] <= _selectedmonth

                && 'Table'[__Period____|].[MonthNo] >= _selectedmonth - 2

        )

    )

VAR _b =

    CALCULATE (

        SUM ( 'Table'[____Volume____|] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[__Period____|].[Year]

                = YEAR ( TODAY () ) - 1

                && 'Table'[__Period____|].[MonthNo] <= 12

                && 'Table'[__Period____|].[MonthNo] > 12 - 3 + _selectedmonth

        )

    )

RETURN

    IF ( _selectedmonth <= 2, _a + _b, _a )

Result:

July:

4.png

February:

5.png

You can download the pbix file from this link: Analyzing Sales Data line items

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors