Setting 12 month display in Timeline by Queryon

[Umlungu]

Hi 

First time query. I am using the Power BI visual called Timeline by Queryon. I want the date to automatically display from 1 July to 30 June. Currently it arranges the display from the first data date to the last data date.

what do you suggest I do?

Regards

[BA_Pete]

Hi @Umlungu ,

You need to set up a calendar table. There's plenty of tutorials available on how to do this, but you will need to create a column for financial periods (I assume this is why you want Jul-Jun).

Once you have your date column for your calendar table in Power Query, you would add a new custom column and put this as the calculation:

if Date.Month([date]) >=7 then Date.Month([date])-6 else Date.Month([date])+6

In DAX it would be this:

IF(
    MONTH(calendar[date]) >= 7,
    MONTH(calendar[date]) - 6,
    MONTH(calendar[date]) + 6
)

This will give you your financial period number for each month.

Once you add a [monthName] field into your calendar table, in the data view for your calendar you can select [monthName], go to 'Sort by column' on the ribbon and sort by [financialPeriod].

Relate calendar[date] to fact[date] in the model screen, then use calendar[monthName] in any visuals. This will sort the month names July-June.

Pete

[Umlungu]

Hi Pete

Thanks for your answer. Will this provide a solution to my current problem: - when i have only one milestone (say 15 Aug 2021) it will appear in the second measure (month) of 12 measures (months)? Current problem is this single milestone (dated 15 Aug 2021) appears in the middle of the calendar display screen.

Regards

Umlungu

[BA_Pete]

Hi @Umlungu ,

I presume you mean that the chart only shows 15th August but no other dates where the measure value is 0?

If so, then all you need to do is add '+ 0' on to the end of the measure that is displayed in the chart. This will force the chart to display all available dates, even if there is no measure value on those dates.

Pete

[Umlungu]

I will try that out Pete, thanks