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

Need help in getting sales for current month for last year

Hello all,

 

I need help in getting the sales of current month last year. For example , I have sales for Jan 2020, I need to get sales for Jan 2019 and so on. My data set is monthly which means that I only have one value for YYYYMM and it is not continuous. I tried to create a calculated date table and link it with the fact table but I am not able to get it. May be I am missing something. Below is the sample data set. Thanks in advance!

 

Month_Num

Month_Trans_Rpt_Dt_Full

Value CY

month_36

1/20/2020

73010

month_35

12/1/2019

75907

month_34

11/1/2019

78968

month_33

10/1/2019

84985

month_32

9/1/2019

74758

month_31

8/1/2019

80185

month_30

7/1/2019

78729

month_29

6/1/2019

76635

month_28

5/1/2019

85164

month_27

4/1/2019

83169

month_26

3/1/2019

86654

month_25

2/1/2019

76082

month_24

1/1/2019

84307

month_23

12/1/2018

81067

month_22

11/1/2018

87173

month_21

10/1/2018

93482

month_20

9/1/2018

83370

month_19

8/1/2018

95491

month_18

7/1/2018

89951

month_17

6/1/2018

91398

month_16

5/1/2018

95126

 

Thanks,

Lakshay

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Need help in getting sales for current month for last year

Hi @lakshay12311,

It's possible if your data is non-contiguous. As a starting point, you can use the technique outlined by SQLBI to create a date table based on your data.

Assuming your table is called Data and you've marked the Month_Trans_Rpt_Dt_Full column as a date data type, the following would work:

Date = 
    VAR MINYEAR = YEAR ( MIN ( Data[Month_Trans_Rpt_Dt_Full] ) )
    VAR MAXYEAR = YEAR ( MAX ( Data[Month_Trans_Rpt_Dt_Full] ) )
    RETURN
        ADDCOLUMNS (
            FILTER (
                CALENDARAUTO( ),
                AND ( YEAR ( [Date] ) >= MINYEAR, YEAR ( [Date] ) <= MAXYEAR )
            ),
            "CALENDAR YEAR", "CY " & YEAR ( [Date] ),
            "MONTH NAME", FORMAT ( [Date], "MMMM" ),
            "MONTH NUMBER", MONTH ( [Date] )
        )

You can now mark this as a date table as normal. Make sure that the table is related as a many-to-one from your Month_Trans_Rpt_Dt_Full column in your Data table and Date in your Date table, and the relationship is so that Date filters Data e.g.:

image.png

Note that I've hidden the Month_Trans_Rpt_Dt_Full column in the tools as you'll want to use the Date table wherever possible.

I'll now create measures for Current Year Sales (rather than using the column) and Last Year Sales, e.g.:

$ Current Year = SUM(Data[Value CY])
$ Last Year = 
    CALCULATE(
        [$ Current Year],
        SAMEPERIODLASTYEAR('Date'[Date])
    )

note that because I did Current Year Sales as a measure, I can reuse it in the Last Year Sales measure. this is handy if you want to change the underlying logic of a measure that has other measures that depend upon it.

I can now add these into, say, a table with suitable fields from the Date table and it should work as you expect, e.g.:

image.png

I've attached a Power BI Workbook (underneath the post) for you to have a look at if you want to explore the workings a bit more, but I'm confident this is what you need.

As a heads-up, it's better to post these kinds of questions in either the DAX or Desktop forums in future. The Developer forum is concerned with extending Power BI via code and not many people frequent it - DAX questions can get answered very quickly (and by smarter people than me 😉 ) if in the correct forum.

I hope this helps you out - good luck!

Daniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
Super User I
Super User I

Re: Need help in getting sales for current month for last year

Hi @lakshay12311,

It's possible if your data is non-contiguous. As a starting point, you can use the technique outlined by SQLBI to create a date table based on your data.

Assuming your table is called Data and you've marked the Month_Trans_Rpt_Dt_Full column as a date data type, the following would work:

Date = 
    VAR MINYEAR = YEAR ( MIN ( Data[Month_Trans_Rpt_Dt_Full] ) )
    VAR MAXYEAR = YEAR ( MAX ( Data[Month_Trans_Rpt_Dt_Full] ) )
    RETURN
        ADDCOLUMNS (
            FILTER (
                CALENDARAUTO( ),
                AND ( YEAR ( [Date] ) >= MINYEAR, YEAR ( [Date] ) <= MAXYEAR )
            ),
            "CALENDAR YEAR", "CY " & YEAR ( [Date] ),
            "MONTH NAME", FORMAT ( [Date], "MMMM" ),
            "MONTH NUMBER", MONTH ( [Date] )
        )

You can now mark this as a date table as normal. Make sure that the table is related as a many-to-one from your Month_Trans_Rpt_Dt_Full column in your Data table and Date in your Date table, and the relationship is so that Date filters Data e.g.:

image.png

Note that I've hidden the Month_Trans_Rpt_Dt_Full column in the tools as you'll want to use the Date table wherever possible.

I'll now create measures for Current Year Sales (rather than using the column) and Last Year Sales, e.g.:

$ Current Year = SUM(Data[Value CY])
$ Last Year = 
    CALCULATE(
        [$ Current Year],
        SAMEPERIODLASTYEAR('Date'[Date])
    )

note that because I did Current Year Sales as a measure, I can reuse it in the Last Year Sales measure. this is handy if you want to change the underlying logic of a measure that has other measures that depend upon it.

I can now add these into, say, a table with suitable fields from the Date table and it should work as you expect, e.g.:

image.png

I've attached a Power BI Workbook (underneath the post) for you to have a look at if you want to explore the workings a bit more, but I'm confident this is what you need.

As a heads-up, it's better to post these kinds of questions in either the DAX or Desktop forums in future. The Developer forum is concerned with extending Power BI via code and not many people frequent it - DAX questions can get answered very quickly (and by smarter people than me 😉 ) if in the correct forum.

I hope this helps you out - good luck!

Daniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors