Sales as on date last year

[srkase]

Hi all

DATE	YEAR	MINOR GROUP	PRODUCT	DEALER	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	TOTAL QTY	TOTALVAL	month
	20212022	ASMSP	425PS	A1	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	APR
	20212022	MINI	3SH	A1	12	0	0	0	0	0	0	0	0	0	0	0	12	35205	APR
	20212022	MINI	3SH	A2	1	0	0	0	0	0	0	0	0	0	0	0	1	15648	APR
	20212022	MINI	4SH	A2	1	0	0	0	0	0	0	0	0	0	0	0	1	16549	APR
	20212022	MINI	4SH	A3	3	0	0	0	0	0	0	0	0	0	0	0	3	62442	APR
	20212022	MINI	5SH	A4	1	0	0	0	0	0	0	0	0	0	0	0	1	21715	APR
	20212022	CBC	BCC	A5	1	0	0	0	0	0	0	0	0	0	0	0	1	22076	APR
	20212022	CBC	BCC	A1	2	0	0	0	0	0	0	0	0	0	0	0	2	50267	APR
	20212022	CBC	BCC	A1	1	0	0	0	0	0	0	0	0	0	0	0	1	25980	APR
	20212022	ASMSP	425PS	A2	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	DEC
	20212022	MINI	3SH	A2	12	0	0	0	0	0	0	0	0	0	0	0	12	35205	DEC
	20212022	MINI	3SH	A3	1	0	0	0	0	0	0	0	0	0	0	0	1	15648	DEC
	20212022	MINI	4SH	A4	1	0	0	0	0	0	0	0	0	0	0	0	1	16549	DEC
	20212022	MINI	4SH	A5	3	0	0	0	0	0	0	0	0	0	0	0	3	62442	DEC
	20212022	MINI	5SH	A1	1	0	0	0	0	0	0	0	0	0	0	0	1	21715	DEC
	20212022	CBC	BCC	A1	1	0	0	0	0	0	0	0	0	0	0	0	1	22076	DEC
	20212022	CBC	BCC	A2	2	0	0	0	0	0	0	0	0	0	0	0	2	50267	DEC
	20212022	CBC	BCC	A2	1	0	0	0	0	0	0	0	0	0	0	0	1	25980	DEC
	20222023	ASMSP	425PS	A3	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	APR
	20222023	MINI	3SH	A4	2	0	0	0	0	0	0	0	0	0	0	0	2	35205	APR
	20222023	MINI	3SH	A5	3	0	0	0	0	0	0	0	0	0	0	0	3	15648	APR
	20222023	MINI	4SH	A1	4	0	0	0	0	0	0	0	0	0	0	0	4	16549	APR
	20222023	MINI	4SH	A1	5	0	0	0	0	0	0	0	0	0	0	0	5	62442	APR
	20222023	MINI	5SH	A2	6	0	0	0	0	0	0	0	0	0	0	0	6	21715	APR
	20222023	CBC	BCC	A2	7	0	0	0	0	0	0	0	0	0	0	0	7	22076	APR
	20222023	CBC	BCC	A3	8	0	0	0	0	0	0	0	0	0	0	0	8	50267	APR
	20222023	CBC	BCC	A4	9	0	0	0	0	0	0	0	0	0	0	0	9	25980	APR
	20222023	ASMSP	425PS	A5	10	0	0	0	0	0	0	0	0	0	0	0	10	5570	DEC
	20222023	MINI	3SH	A1	11	0	0	0	0	0	0	0	0	0	0	0	11	35205	DEC
	20222023	MINI	3SH	A1	12	0	0	0	0	0	0	0	0	0	0	0	12	15648	DEC
	20222023	MINI	4SH	A2	13	0	0	0	0	0	0	0	0	0	0	0	13	16549	DEC
	20222023	MINI	4SH	A2	14	0	0	0	0	0	0	0	0	0	0	0	14	62442	DEC
	20222023	MINI	5SH	A3	15	0	0	0	0	0	0	0	0	0	0	0	15	21715	DEC
	20222023	CBC	BCC	A4	16	0	0	0	0	0	0	0	0	0	0	0	16	22076	DEC
	20222023	CBC	BCC	A5	17	0	0	0	0	0	0	0	0	0	0	0	17	50267	DEC
	20222023	CBC	BCC	A1	18	0	0	0	0	0	0	0	0	0	0	0	18	25980	DEC

The above is my data.  I need to calculate the sales as on date.  

ie.,,,as on today for the previous years...

Last year Sales = CALCULATE(SUM(PROD_CABLE[TOTALCR]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))

but this takes sales upto december 31st .. when the measure is applied .... my fin year is from apr-march..  I need to show the sales upto todays date for the previous year...

I have created a date table also..

Thanks in advance..

REgards

SRK

[v-yanjiang-msft · Accepted solution]

Hi @srkase ,

According to your description, the data you provided seems not related to the formula you provided. So I create a sample.
PROD_CABLE table:

Calendar table:

The two tables are related with Date column.

Here's my solution, create two measures.

YTD =
IF (
    MAX ( 'PROD_CABLE'[TOTALCR] ) = BLANK (),
    BLANK (),
    CALCULATE ( SUM ( 'PROD_CABLE'[TOTALCR] ), DATESYTD ( 'Date'[Date], "3/31" ) )
)

Last year Sales =
CALCULATE ( [YTD], DATEADD ( 'Date'[Date], -1, YEAR ) )

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[speedramps]

Hi 

Firsly you need to format the data from monthly columns into row values.

see how here https://community.powerbi.com/t5/Desktop/How-do-I-split-the-source-data-week-ranges-into-days-accord...

then use a calandar table with offsets

Click here to do free Power BI calendar training 

Especially do the offset training.

You can then slice and dice by offset.  For example

Sales = yourtable[amount]

This year sales = CACULATE( [Sales],  'Calendar'[yearoffset] =0) 

Last year sales = CACULATE( [Sales],  'Calendar'[yearoffset] =-1) 

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[srkase]

But I have huge volume of data in my table.... will take hours to process this... Is there any other alternative?