Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MadhuReddy
Helper I
Helper I

Calendar functions

Hi ,

I have Date column in table in the format '2007-07-20 16:38:36.763' , now I am trying to create calendar in Power BI.

I have used   Master Calendar = CALENDAR(Date(year(MIN('orderhash'[dateadded])),1,1),Date(year(MAX('orderhash'[dateadded])),12,31))

 But when I create table in report view they both does'not match and even measures are not working. How can I change this. 

Please see below for little understanding.  Is it becuase of milli seconds or is that my formulae is wrong?

Please help and thanks in advance. 

Best

Capture.JPG

 

 

1 ACCEPTED SOLUTION

You can do both way but with DAX it would be somethig like:

 

DateOnly = DATE( Year(Table[DateAdded]), Month(Table[DateAdded]), Day(Table[DateAdded[) )

Change the data type to date in modelling tab and then set relationship onthis column with your "master data" date column.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Here is the code for my Date Table. You may need to manually delete and type the quotes. Sometimes hidden characters or something is attached to them when copying from non BI source like this. Notepad should be clean to work in.

 

Also, the reason for a Calendar table is to normalize the dates between tables. In your Visualizations use only dates from the Calendar table. Using dates from tables can stop a table from talking to the visualization as it may not match a date. 

 

Connect the Calendat to all Data tables (primary Data tables, not all). Usually data tables do not connect to each other but connect hrough an intermediary table like the Calendar table.

 

The relationship needs to be:

 

Cardinality:  Many to One

Cross Filter direction: Single

The arrow on the visual schema should flow from the Calendar Table to the tables it is connected to. The link is the Date field in my case as that is the highest resolution in the tables.

 

Dates = GENERATE (

    CALENDAR( DATE( YEAR( TODAY() ) - 3, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),

    VAR startOfWeek = 2 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday   

    VAR currentDay = [Date]

    VAR days = DAY( currentDay )

    VAR months = MONTH ( currentDay )

    VAR years = YEAR ( currentDay )

    VAR nowYear = YEAR( TODAY() )

    VAR nowMonth = MONTH( TODAY() )

    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

    VAR todayNum = WEEKDAY( TODAY() )

    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

  RETURN ROW (

    "day", days,

    "month", months,

"MonthNameShort", FORMAT ( months, "mmm" ),

"MonthNameLong", FORMAT ( months, "mmmm" ),

    "year", years,

    "day index", dayIndex,

    "week index", weekIndex,

    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

    "year index", INT( years - nowYear )

)

))

 

 

 

@Anonymous  I have given those quotes here only they are not available in data source which is SQL. I am using master calendar dates only as you have mentioned but for some reason its not calclating my measures and same measures are working fine with other date formates . Script given by you creates calendar table and not measure right?? .Thanks for your time.Capture2.JPG

 

 

 

Anonymous
Not applicable

Yes, the script will create a calendar table based upon Power BI, not your SQL Database so it should work.

 

When looking at your example it seems Power BI is not recognizing the date format. Have you tried using a simple Dax Date function on it to test? 

@Anonymous Hi, Yes it is not reconizing . No I have not used that function. Could you please guide me.

Thank you 

 

You need to add another column in your data table to for date, you current dateadded column is date and time and the relatedionship with master date is not working because of the time

 

Add another calculated column for dateadded which is just date and then set relationship on this column with master date table.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k Hi, Do i have to add it in modelling tab or query editor ?  What will be the script for calculated column..Could you please guide me. Thank you for your time.

You can do both way but with DAX it would be somethig like:

 

DateOnly = DATE( Year(Table[DateAdded]), Month(Table[DateAdded]), Day(Table[DateAdded[) )

Change the data type to date in modelling tab and then set relationship onthis column with your "master data" date column.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k Ok, I am on modelling tabe and create new tabel is that right ?

there is new column option when you click on home tab in the menu. make sure your correct table is selected in right pane before adding new column



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you ,it is working.  you are a star.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.