Dynamic index based on time hierarchy

[Pallek]

Hi

New to PowerBI and facing some challenges.

I have a dataset like below:

Brand	Month	Year	Sales
A	August	2014	125
B	September	2014	124
C	October	2014	414
A	August	2015	134
B	September	2015	200
C	October	2015	500

Based on the dataset I have created a custom time hierarchy to fit our Financial year, and that works fine. However, I need to be able to see an Index on Month to Month growth and all other elements in my time hierarchy (Month-Quarter-Financial Year).

How do I accomplish this in PowerBI?

Thanks

[v-micsh-msft]

Hi Pallek,

Could you please share more details about the time Hierarchy that you created for the Financial Year?

By the way, what do you mean by saying “an Index on Month to Month growth and all other elements in my time hierarchy”?

If you mean a Continuous period of time, then we could take use of date table with the calendar function.( Returns a table with a single column named “Date” containing a contiguous set of dates). Then use the Year, month function to create the Year and month column.

For Quator, QuarterofYear = ROUNDUP (MONTH([Date])/3,0).

See a thread for reference: https://community.powerbi.com/t5/Desktop/Possible-Tip-Trick-Dynamic-Date-Dimension-Table/td-p/8462

To match the Financial month, if July is the First financial month, then we could create a new column with the formula below:

Financialmonth = If(month(Datetable[date])>=7, month(Datetable[date])-6, month(datetable[date])+6)

FinancialQuarter calculation should be similar with FinancialMonth.

FinancialQuarter = if(ROUNDUP (MONTH([Date])/3,0)>=3, ROUNDUP (MONTH([Date])/3,0)-2, ROUNDUP (MONTH([Date])/3,0)+2)

If any further assistance needed, please feel free to post back.

Regards

[Greg_Deckler]

Do you have a date table that reflects your Financial year? Most likely, that is what you are going to need in order to do time intelligence type stuff. You can use DateStream in the Azure Data Marketplace and there are a lot of other ways to quickly generate a date table.