Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kukszi
Helper I
Helper I

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
Ashish_Mathur
Super User
Super User

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
v-rzhou-msft
Community Support
Community Support

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. 

amitchandak
Super User
Super User

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

Ashish_Mathur
Super User
Super User

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/
TomMartens
Super User
Super User

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.