Is it possible to use PRREVIOUSYEAR FUNCTION with this data?

[firstch]

Hi all 

Please  help me how to use PREVIOUSYEAR  function with this data that not have date table , I really need previous year yields for making report , but I'm not expert in DAX , hope someone can help me 
Thankyou, in advance

[AilleryO]

Hi,

Since you do not have a "proper" date (ie day, month and year), why not just substract 1 to the year to get the previous one ?

With a formula like :

Prev Year Value = 
VAR CurrentYear = SELECTEDVALUE( TabDatas[Year] )
RETURN
CALCULATE( SUM( TabDatas[Value] ) , TabDatas[Year] = CurrentYear -1 )

and get that :

If your data are as simple as the one shown, you might not need to use Time Intelligence functions. If you want to use them then you should create a proper date columns and maybe even a date table.

Hope it helps

[SanketBhagwat]

Hi @firstch .
First create a Date column using your Year Column.
Date=DATE([YearColumn],"01","01").

Then create  a measure for previous Year using PrevYear=PREVIOUSYEAR(DateColumn)


Thanks,
Sanket

If this post helps, then mark it as Accept as Solution and give it a thumbs up.

[firstch]

Hi @SanketBhagwat  when i create date column it pop up me this

[SanketBhagwat]

 Do you have this brackets in your DAX at the end?

[SanketBhagwat]

I believe you have some additional brackets at the end your formula, remove them.
Try and write the formula from scratch.

[SanketBhagwat]

What is that first parameter which you are passsing?
You just need to pass your Year Column as your first parameter.

Check your formula once.
Thanks,
Sanket

[firstch]

@SanketBhagwat  I passing year column that call 'ปี' but the data type format is text 

Thanks 
First

[SanketBhagwat]

Try changing the data format to whole number and let me know if it works.

Thanks,
Sanket

[firstch]

this is my DAX

this is my date column

[SanketBhagwat]

Worked right?

[firstch]

its doesn't show the prevyear Yield sir

[SanketBhagwat]

Why are you getting your year column values as 2557,2558..?
Check that first because the data which you provided has year column values as 2020,2021,2022....

[firstch]

the data that i give is the example but this one is real data both are  same structure sir

[firstch]

it doesn't work sir

[SanketBhagwat]

Can you share a .pbix with some sample data?

[firstch]

@SanketBhagwat  want this as output

this is all data

product	year	yields_tons	harvest_area_SQM
cabbage	2563	138,520	81,543
cabbage	2563	76,009	46,512
cabbage	2563	23,722	33,364
cabbage	2563	19,906	34,440
cabbage	2563	14,928	9,434
cabbage	2563	11,433	27,028
cabbage	2563	8,296	5,169
cabbage	2563	2,888	5,618
cabbage	2564	2,819	3,747
cabbage	2564	2,708	4,147
carrot	2564	242,686	167,504
carrot	2564	130,918	107,222
carrot	2564	75,731	54,840
carrot	2564	33,133	38,298
carrot	2564	30,220	23,580
carrot	2565	30,007	43,806
carrot	2565	1,150	2,208
carrot	2565	1,084	3,576
carrot	2565	1,077	2,613
carrot	2565	893	2,249
carrot	2565	451	567
carrot	2565	301	435
tomato	2562	260	348
tomato	2562	219	328
tomato	2562	192	295
tomato	2557	160	140
tomato	2557	40	68
carrot	2558	234,514	167,004
carrot	2558	124,495	108,257
carrot	2558	71,182	55,192
carrot	2558	31,922	23,155
carrot	2558	30,809	37,815
carrot	2558	28,850	23,283
carrot	2558	24,965	44,163
carrot	2558	9,416	30,852
carrot	2558	7,924	28,655
carrot	2558	6,982	5,066
carrot	2558	6,192	8,878
carrot	2558	4,522	14,309
carrot	2558	4,104	8,390
carrot	2558	3,394	3,470
carrot	2558	3,259	7,250
carrot	2558	1,633	1,638
carrot	2558	1,175	2,202
carrot	2558	1,045	3,537
carrot	2558	972	1,449
carrot	2558	823	2,228
carrot	2558	773	2,082
carrot	2558	577	1,953
carrot	2558	373	631
carrot	2558	326	454
carrot	2558	267	430
carrot	2558	240	354
carrot	2558	165	167
carrot	2558	78	170
carrot	2558	39	72
carrot	2558	1	19
carrot	2559	187,790	171,092
carrot	2559	119,814	127,250
carrot	2559	59,676	56,234
carrot	2559	30,393	37,929
carrot	2559	29,904	24,178
carrot	2559	463	2,238
carrot	2559	251	646
carrot	2559	187	536
carrot	2559	177	442
carrot	2559	138	5,056
carrot	2559	119	126
carrot	2559	52	182
carrot	2559	49	395
carrot	2559	34	87
carrot	2559	1	26
carrot	2560	284,874	173,672
carrot	2560	114,252	130,911
grape	2557	102,908	126,688
grape	2557	48,366	80,088
grape	2557	38,356	54,405
grape	2557	23,931	32,177
grape	2557	16,710	23,656
grape	2557	10,559	13,894
grape	2557	10,270	12,918
grape	2557	8,895	12,387
grape	2557	8,342	20,546
grape	2557	6,895	11,259
grape	2557	4,049	4,855
grape	2557	2,078	7,318
grape	2557	1,685	3,187
grape	2557	1,627	2,815
grape	2557	1,381	2,019
grape	2557	971	1,317
grape	2557	624	886
grape	2557	484	918
grape	2557	483	1,189
grape	2557	406	652
grape	2557	247	449
grape	2557	96	278
grape	2557	11	43
grape	2558	81,660	127,594
grape	2558	31,124	79,359
grape	2558	18,395	33,782
grape	2558	14,264	26,231
grape	2558	13,260	54,119
grape	2558	6,974	12,670
grape	2558	6,215	19,883
grape	2558	5,866	13,858
grape	2558	5,192	11,800
grape	2558	4,787	12,106
grape	2558	3,213	6,933
grape	2558	2,676	4,613
grape	2558	1,266	2,610
grape	2558	1,151	3,059
grape	2558	1,043	1,989
grape	2558	660	1,246
grape	2558	659	1,039
grape	2558	462	895
grape	2558	392	1,109
grape	2558	296	638
grape	2558	246	449
grape	2558	98	305
grape	2558	12	43
grape	2559	68,871	129,339
grape	2559	29,150	80,126
grape	2559	25,226	54,308
grape	2559	15,649	34,917
grape	2559	13,191	26,258
grape	2559	5,952	20,626
grape	2559	5,943	12,670
grape	2559	5,279	13,945
grape	2559	4,280	11,974
grape	2559	4,106	11,774
grape	2559	2,793	6,621
grape	2559	2,472	4,490
grape	2559	1,119	2,534
grape	2559	831	2,924
grape	2559	589	1,161
grape	2559	462	895
grape	2559	450	1,475
grape	2559	434	1,404
grape	2559	249	599
grape	2559	118	449
grape	2559	100	493
grape	2559	91	343
grape	2559	12	43
grape	2560	112,309	129,537
grape	2560	24,897	81,097
grape	2560	24,603	35,047
grape	2560	16,766	26,204
grape	2560	13,899	52,449
grape	2560	4,878	13,780
grape	2560	3,304	9,982
grape	2560	3,237	12,367
grape	2560	1,388	4,271
grape	2560	698	2,948
grape	2560	683	1,369
grape	2560	638	2,292
grape	2560	610	12,218
grape	2560	327	1,447
grape	2560	318	6,621
grape	2560	294	1,429
grape	2560	281	599
grape	2560	277	503
grape	2560	194	518
grape	2560	96	340
grape	2560	85	20,225
grape	2560	83	449
grape	2560	15	60
grape	2561	50,719	129,807
grape	2561	40,240	83,713
grape	2561	24,095	49,815
grape	2561	14,583	36,675
grape	2561	11,399	20,307
grape	2561	8,291	13,615
grape	2561	8,274	27,141
grape	2561	6,642	12,247
grape	2561	5,787	12,871
grape	2561	4,757	10,697
grape	2561	3,307	6,861
grape	2561	2,330	4,052

Thankyou in advance @SanketBhagwat 

firstch

[AilleryO · Accepted solution]

Hi,

Here is the solution calculated witha measure :

Measure PrevYear = 

VAR CurrentYear = SELECTEDVALUE( DataHarvest[year] )
RETURN
CALCULATE( SUM( DataHarvest[yields_tons] ) , DataHarvest[Year]=CurrentYear-1 )

to get that :

[firstch]

Thanks you so much sir, it's work now

😀

[firstch]

how to share pbix file

[firstch]

ok i willl share it please wait .