Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Hide Month with values 0

Hello,

I am having following visual

X-Axis is month from a date hierarchy

Y Axis are calculated measures.

I wanted to hide the months which didn't occur yet i.e i want to show the graph till june.

How do i achieve this?

Please Help

Thanks,

Dharanisree Srinivasan

Ashish_Mathur
4 years ago
Hi,

Ensure you have a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name by the Month number. Ensure that the last date in the Calendar Table is till the date where you have data in your data table. Create a relationship (Many to One and Single) between the Date column in your Data Table to the Date column in your Calendar Table. Create a slicer of Year and select a certain Year. To your visual, drag Month name from the Calendar Table.

Hope this helps.

5 Replies

PaulDBrown
Community Champion
4 years ago
This may not be pertinent in your scenario but beware that filtering out 0s may not provide the desired result (it will also exclude months where the measure returns 0).

An alternative is to calculate the maximum month where there is sales and then use that value as a filter

For example:

Measure for visual = VAR _MX = CALCULATE(LASTNONBLANK('Table'[Month], [Sum Sales]), ALL('Table'[Month])) RETURN IF(MAX('Table'[Month]) <= _MX, [Sum Sales])
Anonymous
Not applicable
4 years ago
Hi,
You can filter your visual to only show results where Loan Funded is greater than 0 (For my example "Costs Amount")

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
- Anonymous
  Not applicable
  4 years ago
  The loan funded here is measure,which is a dynamic value. I guess measures cannot be used on Filters.
  - Anonymous
    Not applicable
    4 years ago
    They can, just drag them to the visual filter pane
Ashish_Mathur
Super User
4 years ago
Hi,

Ensure you have a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name by the Month number. Ensure that the last date in the Calendar Table is till the date where you have data in your data table. Create a relationship (Many to One and Single) between the Date column in your Data Table to the Date column in your Calendar Table. Create a slicer of Year and select a certain Year. To your visual, drag Month name from the Calendar Table.

Hope this helps.

Related content