Custom Calendar

[Learner1]

Hello guys,

I need you guys expertise so I can know what I’m doing wrong because I’m going nuts trying to figure it out.

I use the default calendar and all the graphs work fine. But I need a custom calendar because I need like fiscal year starting September 1 and ending August 31 of the following year which I have no idea how to do it.

So, I saw a youtube video on how to create a custom calendar and I try to follow the steps but there is something wrong, below is the code from the video:

Step1) added the daxcode from the video.

Calendar = ADDCOLUMNS(

    CALENDAR( DATE(2022, 9, 1), TODAY() )

    , "Year", YEAR([Date])

    , "Quarter", QUARTER([Date])

    , "Month Num", MONTH([Date])

    , "Week Num", WEEKNUM([Date])

    , "Week Day", WEEKDAY([Date])

    , "Day", DAY([Date])

    , "Month", FORMAT([Date], "mmmm")

    , "Week", FORMAT([Date], "dddd")

    , "Quarter Number", "Q" & QUARTER([Date])

    , "Year Month", YEAR([Date]) & " " & FORMAT([Date], "mmmm")

    , "Year Month Sort", YEAR([Date]) & MONTH([Date])

    , "Year Quarter", YEAR([Date]) & " " & "Q" & QUARTER([Date])

    , "Year Quarter Sort", YEAR([Date]) & QUARTER([Date])

    , "Fiscal Year", YEAR(EDATE([Date], 11))

    , "Fiscal Month", MONTH(EDATE([Date], 11))

    , "Fiscal Week", WEEKNUM(EDATE([Date], 11))

    , "Fiscal Quarter", QUARTER(EDATE([Date], 11))

    , "Last 6 Months", IF( EOMONTH([Date], 0) > EOMONTH(TODAY(), -6) && [Date] <= TODAY(), TRUE() )

    , "Last 6 Months Complete", IF( EOMONTH(TODAY(), 0) <> TODAY()

        , IF( EOMONTH([Date], 0) > EOMONTH(TODAY(), -7) && [Date] <= EOMONTH(TODAY(), -1), TRUE() )

        , IF( EOMONTH([Date], 0) > EOMONTH(TODAY(), -6) && [Date] <= TODAY(), TRUE() )

    )

)

Step2) Once I added the code I can see the custom calendar:

Step3) Then I make the relationship to the other table column called Created which is a type of date/time. So the “Created column”  and my “Date column” from my custom calendar table have a relationship.

Step4) Then when I add a slicer, it shows the years as expected since I have it selected.

My question is how come it does not show any data or the other graphs neither change when I select 2022. It seems the custom calendar is not picking up the data from the other table even though I made the relationship. Is there something wrong with the relationship?

Any feedback is appreciated.

[v-tangjie-msft]

Hi @Learner1 ,

The visual object is normal through my tests. Please delete the date slicer and then check if the visual object has the data displayed properly.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[lbendlin]

If your fiscal year starts in September then your EDATE needs to be set to 8, not 11.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...