Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filter values x-axis dynamically

Hi! I created a monthly percent difference chart using Power BI's quick measure, month-over-month change. My problem is the value for the next month (-100%) always appear in the chart. I only want up...
Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Anonymous

     

    I Answer in PM. The answer to avoid this is:

     

    Cost MoM% = 
IF(MAX('Table1'[Date_new])<>BLANK();
IF(
	ISFILTERED('Table1'[Date_new]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('Table1'[Cost]);
			DATEADD('Table1'[Date_new].[Date]; -1; MONTH)
		)
	RETURN
		DIVIDE(SUM('Table1'[Cost]) - __PREV_MONTH; __PREV_MONTH)
))

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Vvelarde,

 

It says 

 

Vvelarde's avatar
Vvelarde
Community Champion
8 years ago

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Vvelarde

     

    Still says ; is invalid to me. What's the difference of using a ' ; ' and a ' , ' ?

     

    Can you also explain what does adding IF(HASONEVALUE()) does to the formula?

     

    Thanks so much! : )

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      8 years ago

      Anonymous

       

      The ; and , is just regional settings.

       

      If you Formula works with , all should be with ,

       

      HASONEVALUE evaluate in your scenario if you had a date in that context.  In April you don't have dates so the result is blank and don't show in the visual.

       

      Regards

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Vvelarde

         

        Okay, I understand that part now. Thanks Victor! But the thing is the month on month change quick measure always gives the final row (the -100%).  What should I do so that the last row will not end up showing in my chart? (Thanks for answering my endless questions :) )