Calender Year to Financial Year

[TadepalliSaiKum]

Hi every one,

Suppose I gave data from Jan2018-Sep2019 and I am representing the data through charts. I need the representation in such a way that I need to see the results Year wise, Quarter wise and Month wise(i.e. the time period should be Finanical Year like Year wise means Apr2018-March2019) and if i drill down that i need to get Quarters in that Financial Year(i.eAPr2018-Jun2018 "Q1") and on further drill down, i need to get monthly data.

Is there any possibility for the above process? 

[v-xicai · Accepted solution]

Hi @TadepalliSaiKum ,

You can create calendar table like DAX below, then create relationship between your fact data table and calendar table on date field. You may click Drill down of visual to get lower date hierarchy.

Calendar = CALENDAR(MIN(Table1[Date]),MAX(Table1[Date]))  

In your scenario, it maybe codes: Calendar = CALENDAR(DATE(2018,1,1),DATE(2019,9,30)).  

Or you may use codes: Calendar =CALENDARAUTO().

Best Regards,

Amy

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

[adambhappy]

Assuming your fact table has a date column it's simply a matter of using a date table with the data about fiscal years and fiscal quarters. Hopefully the date table below works for your needs

dates = 
VAR vMinYear = 2010
VAR vMaxYear = 2030
RETURN
ADDCOLUMNS (
        CALENDAR(DATE(vMinYear;1;1);DATE(vMaxYear;1;1));
    "Calendar Year"; "CY " & YEAR ( [Date] );
    "Calendar Year Number"; YEAR ( [Date] );
    "Fiscal Year"; "FY " & IF(MONTH([Date])<4;YEAR([Date])-1;Year([Date]));
    "Fiscal Year Number"; IF(MONTH([Date])<4;YEAR([Date])-1;Year([Date]));
    "Fiscal Year Month Number";IF(MONTH([Date]<4);Month([Date])+10;Month([Date]));
    "Fiscal Quarter"; IF(Month([Date])>=10;"Q3";IF(Month([Date])>=7;"Q2";IF(Month([Date])>=4;"Q1";"Q4")));
    "Fiscal Quarter Number"; IF(Month([Date])>=10;3;IF(Month([Date])>=7;2;IF(Month([Date])>=4;1;4)));
    "Month Name"; FORMAT ( [Date]; "mmmm" );
    "Month Number"; MONTH ( [Date] )
)

[TadepalliSaiKum]

Dear Adambhappy,

Thanks for your quick reply. When I apply th ebaove code for my data set it is showing some error and the error comes out to be "The expression refers to Multiple columns. Multiple Columns cannot be converted to a scalar values".

What can be done for this issue?

[danextian]

@TadepalliSaiKum the formula given by @adambhappy was meant to be entered as a calculated table. This will return a sample data that follows a fiscal year.