Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshi
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.
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 =
Hi @bideveloper555 ,
Can you share sample data in table format and the screenshot of your data model.
Regards,
Harsh Nathani
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |