Forum Discussion

Totte67's avatar
Totte67
Frequent Visitor
10 years ago

Broken Year

Hi, i am creating some Power BI reports for a customer, they use Microsotft Dynamics Nav, The customer have a broken calender year. I have solved it a littile bit, but when you click on a Visual ...
Need Help
Totte67's avatar
Totte67
Frequent Visitor
10 years ago

Sorry, I haven’t answered, I have been on a short vacation.

Very thankful you taking the time to help me.

The calendar year this customer have is October to September


The problem I have is that when I put up three “Visualizations Column Chart”

One for year, one for quarter and one for month.

 

When I look to the year everything is right, but the quarter and the month following the original calendar year.

 

This is the Script I used to create the date table in “SQL Server Data Tools for Visual Studio 2013”
Declare @StartDate datetime

Declare @EndDate datetime

Set @StartDate = '2006-10-01'

Set @EndDate = GETDATE()

Declare @LoopDate datetime

Set @LoopDate = @StartDate

While @LoopDate <= @EndDate

Begin

Insert Into dbo.DimDate Values

            (

            Cast(Convert(Varchar(8), @LoopDate, 112) AS Int),

            @LoopDate,

            YEAR(@LoopDate),

            DATEPART(qq, @LoopDate),

            MONTH(@LoopDate),

            DATENAME(mm, @LoopDate),

            DAY(@LoopDate),

            DATEPART(DW, @LoopDate),

            DATENAME(DW, @LoopDate),

            Case

            When MONTH(@LoopDate)<1 Then YEAR(@LoopDate)

            Else YEAR(@LoopDate) +1

            End,

            Case

            When MONTH(@LoopDate) IN (10, 11,12) Then 1

            When MONTH(@LoopDate) IN (1, 2, 3) Then 2

            When MONTH(@LoopDate) IN (4, 5, 6) Then 3

            When MONTH(@LoopDate) IN (7, 8, 9) Then 4

            End

            )

            SET @LoopDate = DATEADD(dd, 1, @LoopDate)

End;

 

I now think I see the problem in Case When MONTH(@LoopDate)<1 Then YEAR(@LoopDate)

            Else YEAR(@LoopDate) +1

I think it should be 10.

What you think?

What I heard is that many Companies have the same problem,
so maybe we can solved some problems for other also,
I cannot test this right now but I will son and come back to you. Thanks.

 

Best regards

Totte67

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

Totte67

 

If the customer's calendar year is from Oct to next year's Sep, then it should be "When MONTH(@LoopDate)<10". How are visuals not working with the broken calendar? Do they work after fixxing "When MONTH(@LoopDate)<10"?

  • Totte67's avatar
    Totte67
    Frequent Visitor
    10 years ago

    Thanks,

    I how now tested the new script but when I look to the result in the table I see that only the last column is right FiscalQuarter for october is 1, but the column Date_CalenderQuarter is 4


    Here is the script I used
    Use [Power BI]
    Declare @StartDate datetime
    Declare @EndDate datetime
    Set @StartDate = '2006-10-01'
    Set @EndDate = GETDATE()
    Declare @LoopDate datetime
    Set @LoopDate = @StartDate
    While @LoopDate <= @EndDate
    Begin
    Insert Into dbo.DimDate Values
    (
    Cast(Convert(Varchar(8), @LoopDate, 112) AS Int),
    @LoopDate,
    YEAR(@LoopDate),
    DATEPART(qq, @LoopDate),
    MONTH(@LoopDate),
    DATENAME(mm, @LoopDate),
    DAY(@LoopDate),
    DATEPART(DW, @LoopDate),
    DATENAME(DW, @LoopDate),
    Case
    When MONTH(@LoopDate)<1 Then YEAR(@LoopDate)
    Else YEAR(@LoopDate) +1
    End,
    Case
    When MONTH(@LoopDate) IN (10, 11, 12) Then 1
    When MONTH(@LoopDate) IN (1, 2, 3) Then 2
    When MONTH(@LoopDate) IN (4, 5, 6) Then 3
    When MONTH(@LoopDate) IN (7, 8, 9) Then 4
    End
    )
    SET @LoopDate = DATEADD(dd, 1, @LoopDate)
    End;

    • Eric_Zhang's avatar
      Eric_Zhang
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      Totte67

       

      What is the problem with Date_CalenderQuarter being 4? October should be in Quarter 4 in a nature year.