Time Matrix Using Only Month

[JSteele]

Hello,

I am trying to create a calendar to visualise fruit seasons. I have created mock up in excel to show what I'm trying to achieve:

I am a PowerBI newbie and am having trouble achieving this in PowerBI. For reference, the finished product will be more detailed than this with numerous commodities, countries, and fruit activities, so it needs to be in PowerBI to make use of filters, slicers, etc.

The main issue I'm having is that I don't want/need to include years in the data as it repeats each year. Adding data with years is leading to issues with seasons that run over the start of the year (e.g. november to february). In these situations I'd like the chart to show a bar from jan to feb and another bar from nov to dec. 

Hope this makes sense, any feedback on how to best achieve this is much appreciated.

Thanks!

[v-easonf-msft]

Hi, @JSteele 

If your field has a hierarchy, then in the matrix you should be able to place your date field as follows to keep only the hierarchy 'month'.

You could also try creating a column 'Month' and applying it to the matrix 'Columns'.

Month = FORMAT(Table1[Date],"MMMM")

Best Regards,
Community Support Team _ Eason

[JSteele]

Hi @v-easonf-msft 

Thank you for your help, I think I've sorted my month problem now 🙂

I have ran into another issue if you are able to help with that too.

I am trying to write an if statement to identify if a season runs in a month, with the idea being if it does it returns a 1, and if it doesn't it returns a 0, so I can then conditional format my matrix to create the line visuals. 

My current statement clearly isn't working, but I'm not sure how to correct it/if what I'm trying to achieve is possible.

Any help would be much appreciated.

Thanks!

[danextian]

Hi @JSteele ,

You can write a measure similar to below to identify whether a season runs in a month but without knowing how your data model looks and a sample data, this may  not work:

In Season =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Table[Column] ),
        ALLEXCEPT ( Date, Date[Month Number] )
    ) > 1,
    0,
    1
)

[JSteele]

@danextian Thanks for your response.

My model contains a start and end date for each season as shown below. 

Is the measure you suggested still applicable?

Thanks

[danextian]

Hi @JSteele ,
Several questions:

• How is your data related to your Date table? I don't see any month number column on your screenshot. Is there an existing relationship between the two?
• Which table and column do you use for the month name in your matrix?

With just your current data, what I can suggest is to create a calculated column to calculate the number of months from start to end.  Jan 31, 2021 and Feb 1, 2022 will be considered two months.  Here's a sample:

Diff = 
DATEDIFF ( 'Date'[Start], 'Date'[End] + 1, MONTH )
//+1 to compute for the total number of days from start to end

 If you're going to use this in a matrix and there are more than one instance of the field in the matrix row, create a measure MAX (Date[Diff]). Change the table and column names accordingly.

[JSteele]

Hi @danextian 

1. I have a seperate date table that includes the month number and have created a relationship between that and my seasons data table

2. For the month name I'm using the month column from my date table with a date hierachy in place

[danextian]

Hi @JSteele ,

How is your date table linked to the seasons table? What columns link between the two (please show a screenshot of the relationship view)? Please post a sample data that we can just copy-paste instead of just a screenshot.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/2500104#M8890...

[JSteele]

@danextian 

Current relationship:

Sample data:

Country	Region	Hemisphere	Commodity	Activity	Start date	End date
US - PNW	NAM	North	Apple	Picking	8/1	11/30
US - CA	NAM	North	Apple	Picking	7/1	10/31
US - East	NAM	North	Apple	Picking	7/1	10/31
Chile	LATAM	South	Apple	Picking	2/1	5/31
Poland	Europe	North	Apple	Picking	8/1	10/31
Turkey	Europe	North	Apple	Picking	8/1	10/31
Iran	Europe	North	Apple	Picking	8/1	10/31
Italy	Europe	North	Apple	Picking	8/1	10/31
France	Europe	North	Apple	Picking	8/1	10/31
Russia	Europe	North	Apple	Picking	8/1	10/31
China	Asia	North	Apple	Picking	7/1	10/31
India	Asia	North	Apple	Picking	6/1	9/30
Australia	Oceania	South	Apple	Picking	1/1	3/31
New Zealand	Oceania	South	Apple	Picking	2/1	4/30

[danextian]

hI @JSteele,

Based on your data, there are two possible approaches I can think of: using SUMX if country repeats and MAX if otherwise. Please refer to this pbix:  https://drive.google.com/file/d/1Lo65qeC3k1349D2QbWhirdInmYdeoo29/view?usp=sharing 

[JSteele]

Thanks @danextian!

Sorry if I'm being dumb here, but if those solutions produces a 1 on the month when a season starts, how do I go about making it so that a 1 is shown for every month that it's in season? E.g. Australia would show 1s for Jan and Feb.

[amitchandak]

@JSteele , The problem with usch idea is that without year, the data from nov to feb will shown in two split jan-feb and Nov-dec .

So the best idea is to use Month Year column or year and month columns.

I also check you can not sort month on month year, so that month can repeat without year