Current Year Month vs Last Year Month Cluster Column Chart

[Anonymous]

Hi everyone,

I have a cluster column chart in which I compare the number of units sold for a month vs that month last year.  I have my date range set for 2019 - Present.   

Since there is only comparable data from Jan 2020 to April 2021, I want to find way to not show 2019 and months in the future which only have the 2020 data visible with no 2021 data to compare to.  

I would prefer not increase the size of the bars so the data is pushed out of view.  Anyone have any ideas?

Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please try to write your measure something like below.

newmeasure = if ( isblank([unitsoldmeasure]), blank(), your lastyearmeasure)

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

Hi @Jihwan_Kim ,

Currently I'm using a Current Year MTD

CALCULATE(SUM(units sold) , DATESMID (sold date)

&

Last Year MTD

CALCULATE(SUM(units sold), DATEADD(sold date.date, -1, YEAR))

I've place both of these as values for my chart.  Should I try modifying them with the ISBLANK?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Sorry that I don't fully understand your second question.

If it is OK with you, can you share your sample pbix file's link ?

[Anonymous]

Unfortunately, I can't share due to privacy restrictions. 

Here are two screenshots of the chart.  The first one is my initial attempt.  As you can see, 2019 and most of 2021 have nothing to compare to and I'd like to make them not visible.

The second one is using your idea of ISBLANK which takes care of the future 2021 months but not the 2019.

Ideally I would like to turn this into a rolling 12 month so there would be no manually updates needed

[Jihwan_Kim]

Hi,

Thank you for your feedback.

The alternative option to using isblank(   ) is restricting the visualization by using Min(sold-date column) and Max(sold-date column).

For instance, if ( Min(datetabledate) <= Max(sold-date column) && Max(datetabledate) >= Min(sold-date column), yourmeasure )

This will dynamically correspond with the changes of the max sold date in the main table.

Thank you.

[Anonymous]

So do I need a separate date table for this to work of can I use what I already have?

[Jihwan_Kim]

If you do not have a dim-date-table, then, in my opinion, using isblank(   ) is the way.

[Anonymous]

Using ISBLANK, I still have 2019 showing in the chart.  Is there a way to 2019 filtered out of the chart?

[Jihwan_Kim]

Hi,

Please try to use a visual level filter, and filter out the year that you do not want.

You can select your visualization, and to the Filters Pane, and search for the date/year related filter. Here, you can filter your visualization.

Thank you.