Need help in getting sales for current month for last year

[Anonymous]

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

[dm-p · Accepted solution]

Hi @Anonymous,

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

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

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