Hello
I use Melissa’s calendar table and was wondering why the last date of the calendar table is not the day I expected.
I get the start date and Enddate from a sales table
the end date is correct 31/05/2024 (see screen shot) but the calendar stops at 30/05/2024, So I’m missing 1 day.
any idea what I do wrong and how to fix the laste date?
kind regards
Roger
Hi @Roger ,
While waiting for the community to review and respond to your issue, take advantage of Data Mentor . It offers a wealth of tools and resources that could provide immediate solutions and enhance your report-building efficiency.
Cheers,
Enterprise DNA Support Team
You’re using the earliest value of the HISTORIEK[Periode] column as the value for your StartDate parameter. That seems fine, but you’re doing something different with your Enddate parameter. It’s finding the last day of the month for Source. Unfortunately, it isn’t clear how Source is defined based only on your screenshots.
You probably mean to define your Enddate parameter as something like:
Date.EndOfMonth( List.Max( HISTORIEK[Periode] ))
which returns the last day of the month associated with the latest date from the list of dates captured in HISTORIEK[Periode].
is your source data in Date or DateTime format?
Try wrapping both your StartDate and EndDate functions in Date.From - this will force them to convert to a date only period, instead of a datetime period which might be suffering from rounding issues…
Date.From( List.Min( HISTORIEK[Periode] ))
Date.EndOfMonth( Date.From( Source ))
Which version of the date table are you using @Roger?
Please grab the latest version here:
Extended Date Table (Power Query M function).
I used a older version of Melissa’s date table and downloaded the latest version and will test it later. But I don’t think the problem is in the table. The start date is picked from the historiek table and is correct.
I used a older version of Melissa’s date table and downloaded the latest version and will test it later. But I don’t think the problem is in the table. The start date is picked from the historiek table( 2022 & 2023 ) and is correct. The end date is picked from the actual table called “maandrapporten” ( year 2024 ) the latest dat is 01/05/2024. Both tables are identical in structure. I used List.Max() and wrapped it in the function Date.EndOfMonth() with the correct result 31/05/2024 but the date table stops at 30/05/2024. Thanks all for your suggestions
Just thought … time zone is likely the culprit … the Power BI Service uses UTC time, which is likely different from the time zone of the data …
Greg