Fiscal Year Data - Month wise data

[KarishmaDoshi]

I am new to Power Bi. I have data from April 2015 to January 2020. The fiscal year is April-March. The data is monthly and not daily eg. sales for April 2015, May 2015, June 2015 and not daily date basis. I have created new columns as below:

Date Table= CALENDARAUTO(03)

Month Name= [Date].[Month]

Year= [Date].[Year]

Month No= [Date].[MonthNo]

Qtr No= CEILING([Month No.],3)/3

Qtr= "Qtr "&[Qtr No]

FY Qtr= IF([Month No.]<4, "Qtr 4","Qtr "&[Qtr No]-1)

FY= IF([Month No.]<4, "FY "&[Year]-1&"-"&[Year],"FY "&[Year]&"-"&[Year]+1

The above data is fetching data for daily date wise which is not letting my other table connect to this table. I want to compare YOY growth degrowth - absolute numbers and percentage, YTD revenue, etc and I know that sameperiodlastyear function won't be helpful. Can someone please guide me on how to do it. I can share the data if that is what will help.

[amitchandak · Accepted solution]

If you would like to time intelligence, Then you need continuous dates. So try to have a date in your tables.

Still, You can get the month table from the date table

month Date = SUMMARIZE('Date Table',
'Date Table'[Month Name],
'Date Table'[Year],
'Date Table'[Month No],
'Date Table'[Qtr No],
'Date Table'[Qtr],
'Date Table'[FY Qtr],
'Date Table'[FY],
'Date',Min('Date Table'[Date]))

[v-alq-msft]

Hi, @KarishmaDoshi 

Typically, your data model will need a calendar table with continuous dates, which is important for time intelligence functions to work properly. I wonder if there is some error message. Please make sure the data type from two tables are all date when you connect them. If possible, please show us your sample data. Do mask sensitive data before uploading.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

Very difficult to quite grasp what you are going for without seeing some sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, I often create a column like the following in my date tables to allow other table to connect to it: 

YearMonth = YEAR([Date]) & FORMAT(MONTH([Date]),"00")

Values are output as 202001 for example

Now, with all that being said, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak · Accepted solution]

If you would like to time intelligence, Then you need continuous dates. So try to have a date in your tables.

Still, You can get the month table from the date table

month Date = SUMMARIZE('Date Table',
'Date Table'[Month Name],
'Date Table'[Year],
'Date Table'[Month No],
'Date Table'[Qtr No],
'Date Table'[Qtr],
'Date Table'[FY Qtr],
'Date Table'[FY],
'Date',Min('Date Table'[Date]))