Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bideveloper555
Helper IV
Helper IV

YOY on card

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.

IdYearMonth Current YEAr  PreviousYear YOY%
12020December16.00105.0052.40%
22020December 60.00 
32020December44.0085.00-48.20%
42020December 110.00 
52020December685.00  
62020December100.0035.00-57.10%
72020December 250.00 
82020December 20.00 
92020December185.00155.00-11.30%
102020December 65.00 
112020December150.00375.00-60.00%
122020December 165.00 
132020December150.00345.00-56.50%
142020December10.0080.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 

IdYearMonth Current YEAr  PreviousYear YOY%
12020December16.00105.0052.40%
32020December44.0085.00-48.20%
52020December685.00  
62020December100.0035.00-57.10%
92020December185.00155.00-11.30%
112020December150.00375.00-60.00%
132020December150.00345.00-56.50%
142020December10.0080.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

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

 

MFelix_0-1613748163167.png

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

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

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:

 

MFelix_0-1613748163167.png

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

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

 

Thank you MFlelix for the help.

i been struggling for quite some days.

Have a wonderful day.

 

Thank you Harsh.

harshnathani
Community Champion
Community Champion

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)

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.
 

Hi @bideveloper555 ,

 

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

 

Regards,

Harsh Nathani

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.