YOY on card

[bideveloper555]

hi

i want to exlcude records from YOY measure if there is no value for current year/month based on user slicer selection.

I did write a measure,which was working as it supposed to but requirement is to avoid any stores if they dont have sales in month-year.

This i can filter on table using current year greater than 0 and i get YOY total fine. but i want on card.i have no options to filter on card.

below is sample data.

Id	Year	Month	Current YEAr	PreviousYear	YOY%
1	2020	December	16.00	105.00	52.40%
2	2020	December		60.00
3	2020	December	44.00	85.00	-48.20%
4	2020	December		110.00
5	2020	December	685.00
6	2020	December	100.00	35.00	-57.10%
7	2020	December		250.00
8	2020	December		20.00
9	2020	December	185.00	155.00	-11.30%
10	2020	December		65.00
11	2020	December	150.00	375.00	-60.00%
12	2020	December		165.00
13	2020	December	150.00	345.00	-56.50%
14	2020	December	10.00	80.00	-87.50%
		Total	1,340	1,850
		Ttoal	1,340	1,180
				160	-                                       510
			YOY%	14%	-28%
				I Want to get -14%	not 28%

When i use filter on current year 

Id	Year	Month	Current YEAr	PreviousYear	YOY%
1	2020	December	16.00	105.00	52.40%
3	2020	December	44.00	85.00	-48.20%
5	2020	December	685.00
6	2020	December	100.00	35.00	-57.10%
9	2020	December	185.00	155.00	-11.30%
11	2020	December	150.00	375.00	-60.00%
13	2020	December	150.00	345.00	-56.50%
14	2020	December	10.00	80.00	-87.50%
		Total	1,340	1,850
		Ttoal	1,340	1,180
			YOY%	14%	-28%
				I Want to get -14%	not 28%

I want to show YOY -14% on card but my card show -28%

Thanks

[MFelix · Accepted solution]

Hi @bideveloper555 ,

Create the following measures:

Previous year = 
SUMX (
    VALUES ( 'Table'[Id] ),
    IF (
        ISBLANK ( SUM ( 'Table'[ Current YEAr ] ) ),
        BLANK (),
        CALCULATE (
            SUM ( 'Table'[ Current YEAr ] ),
            SAMEPERIODLASTYEAR ( 'Date'[Date] )
        )
    )
)


YOY % = DIVIDE([Previous year] - SUM('Table'[ Current YEAr ]) ,[Previous year])

Now you can use on a table or a card:

Believe that on your image some calculationa are not exactly at line level, but the syntax should be similar to the ones above.

[bideveloper555]

hi

on same data set, am trying to calculate 3 months growth,6 months growth.

Logic,

Only records should be included in 3 months growth.

user selects 2020 mar

but some stores might not have sales in jan but have in feb,mar 2020. these should be exlcuded.

only stores who has consective 3 months should be calculated. jan,feb, mar 2020

[bideveloper555]

Thank you MFlelix for the help.

i been struggling for quite some days.

Have a wonderful day.

Thank you Harsh.

[harshnathani]

Hi @bideveloper555 ,

You can use this DAX measure to get YOY

YOY Card = 

var _previousyr = SUMX(FILTER(ALL('Table'[ Current YEAr ],'Table'[ PreviousYear ]),NOT(ISBLANK('Table'[ Current YEAr ] ))),'Table'[ PreviousYear ])
var _currentyr = SUM('Table'[ Current YEAr ])

RETURN

1- DIVIDE(_currentyr,_previousyr)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[bideveloper555]

Thank for your reply.

Sorry for mis-representation. Actually Previous year = 

CALCULATE(SUM('Table'[Current YEAr'),SAMEPERIODLASTYEAR('Date'[Date]))

Orginal table i have

ID  period     value as current year

1   2020-12     10.

so please ignore previous year value as it comes from measure.

[harshnathani]

Hi @bideveloper555 ,

Can you  share sample data in table format and the screenshot of your data model.

Regards,

Harsh Nathani