calculate sales in base of month term

[Anonymous]

Hello,

I am writing because I've been working on solve this problems for many days.  The issue is I have a list of customers and every customer has a term month date, this mean that for customer 1 the sales period is from january to december(term 12), customer 2 the sales period is from july to last day of june, finally customer 3 has september his sales period wil be from october to the last day of september.

If I am in the year 2020:

sales period of customer 1 will be january 2020 to december 2020

sales period for customer 2 wil be july 2020 to december 2020

sales period for customer 3 wil be October 2020 to december 2020

Also the complexity is I have to control this with the year and month selector that make imposible to get the correct result. For example if I select the month october customer one have calculate sales from january to october, customer 2 sales from july to october and customer 3 sales only from october

CustomerID	Term (month number)	Sales Last Year	Sales Current Year
1	12
2	6
3	9

Sales table is a simple table by month and customer id.

CustomerID	Date	Amount
1	01/01/2019	1,374
1	01/02/2019	3,918
1	01/03/2019	7,109
1	01/04/2019	1,777
1	01/05/2019	7,307
1	01/06/2019	217
1	01/07/2019	1,763
1	01/08/2019	368

the result is calculate the sales in base of term period for the last year and the current year. Only care the current year because the last year can be a complete value that I can calculate in a calculate column 🙂

Hope i explain good, any doubts let me know 

https://www.dropbox.com/s/k6pnvkkpgkc2q5o/data%20sales.zip?dl=0 

[Anonymous]

thanks!! 😄

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ，

Would you please try the following two measure :

Sales CY =
SWITCH (
    MAX ( Data[CustomerID] ),
    1,
        CALCULATE (
            SUM ( Sales[Amount] ),
            DATESBETWEEN (
                'Date'[Date],
                DATE ( YEAR ( TODAY () ), 1, 1 ),
                MAX ( 'Date'[Date] )
            )
        ),
    2,
        CALCULATE (
            SUM ( Sales[Amount] ),
            DATESBETWEEN (
                'Date'[Date],
                DATE ( YEAR ( TODAY () ), 6, 30 ),
                MAX ( 'Date'[Date] )
            )
        ),
    3,
        CALCULATE (
            SUM ( Sales[Amount] ),
            DATESBETWEEN (
                'Date'[Date],
                DATE ( YEAR ( TODAY () ), 9, 30 ),
                MAX ( 'Date'[Date] )
            )
        )
)



Sales LY =
SWITCH (
    MAX ( Data[CustomerID] ),
    1,
        CALCULATE (
            SUM ( Sales[Amount] ),
            SAMEPERIODLASTYEAR (
                DATESBETWEEN (
                    'Date'[Date],
                    DATE ( YEAR ( TODAY () ), 1, 1 ),
                    MAX ( 'Date'[Date] )
                )
            )
        ),
    2,
        CALCULATE (
            SUM ( Sales[Amount] ),
            SAMEPERIODLASTYEAR (
                DATESBETWEEN (
                    'Date'[Date],
                    DATE ( YEAR ( TODAY () ), 6, 30 ),
                    MAX ( 'Date'[Date] )
                )
            )
        ),
    3,
        CALCULATE (
            SUM ( Sales[Amount] ),
            SAMEPERIODLASTYEAR (
                DATESBETWEEN (
                    'Date'[Date],
                    DATE ( YEAR ( TODAY () ), 9, 30 ),
                    MAX ( 'Date'[Date] )
                )
            )
        )
)

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYff9aeBNTRKgaDn9c4jk5wB9eKqYMxxSEYhtUUxcemVjA?e=VTE1Et

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

Best Regards,

Dedmon Dai

[Anonymous]

Thanks so much !! @v-deddai1-msft 🙂