Broken Year

[Totte67]

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 the report dont follow the broken year,
I have put in a date tabel that I have get the right data for Year, Qty and mounth.

Many thanks for all suggestions.

//Totte67

[Eric_Zhang]

---

@Totte67 wrote:

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 the report dont follow the broken year,
I have put in a date tabel that I have get the right data for Year, Qty and mounth.

Many thanks for all suggestions.

//Totte67

---

@Totte67

What do mean a broken calender year and how does the visual not work with that?  What is the expected output, can you be more specific?

Regarding uploading an attachment, you upload any  to a network storage and share the link. In this case, data in SQL Server, you can even post the table DDL and insert statements.

Not quite clear about your scenario, usually when having problem in date, a calendar table would be of help. Check How to create a date table in Power BI. Create a calendar table and map a many to one relationship between calendar table and yours, then use the date column in calendar as the axis in visual.

[Totte67]

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]

@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]

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]

@Totte67

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

[CheenuSing]

@Totte67

Can you please share sample data ad the output expected.

Cheers

CheeenuSing

[Totte67]

Hi, I new on this forum I can not found how to send a attacment.
So I send a printsreen from the SQL sever I use Posting date from the Fact table and the database is from
"Microsoft Nav Dynamics"

Thanks!
Best regards

Totte67