How to handle infinty values based on Date

[ys034]

Hello Power BI Community

I have a question regarding Infinity values
In my matrix I want the market share in the column for 2021 to show me the percentages for last year.

My Measures: 

Grand Total Sales LY = CALCULATE(SUMX(VALUES('DataTable'),'DataTable'[Total Sales LY]),filter(ALLSELECTED('DataTable'), 'DataTable'[Date].[Year] = MAX('DataTable'[Date].[Year])),FILTER(ALLSELECTED('DataTable'),'DataTable'[Country] = MAX('DataTable'[Country])))

MarketShareFilter LY = CALCULATE([Grand Total Sales LY],All('DataTable'[Brand]))

MarketShare LY = CALCULATE([Total Sales LY]/[Grand Total Sales LY])

[ValtteriN]

Hi,

The infinity is caused here by division. As a best practice you should use DIVIDE instead of "/". For LY calculation you can link your fact table to a calendar and create DAX like this:

LY measure = CALCULATE([Your Measure],SAMEPERIODLASTYEAR('Calendar'[Date]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[amitchandak]

@ys034 , Try like

change this first

Grand Total Sales LY = CALCULATE([Total Sales LY],allselected())

then

divide([Total Sales LY],[Grand Total Sales LY],0)

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[ys034]

unfortunately it did not work I have tried it with two measures at a time

in the first picture with the measure

  MyMeasure: Grand Total Sales LY = CALCULATE([Total Sales LY],allselected())

and in the second measure I applied another AllSelected filter in the measure

MyMeasure= Grand Total Sales LY = CALCULATE([Total Sales LY],ALLSELECTED(),FILTER(ALLSELECTED('DataTable'),'DataTable'[Country] = MAx('DataTable'[Country])))

i hope anybody can help me out i would be grateful

[Icey]

Hi @ys034 ,

How about this:

Grand Total Sales LY =
CALCULATE ( [Total Sales LY], ALLEXCEPT ( 'DataTable', 'DataTable'[Country] ) )

If the above doesn't work, please share us some sample data for test. For your data security, please don't contain any sensitive information.

Reference: 

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey