Forum Discussion
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 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
7 Replies
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 regardsTotte67
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
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.
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 datetimeDeclare @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
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"?
