cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
villasenorbritt
Helper I
Helper I

Date

This is what I started with:

villasenorbritt_0-1653397709017.png

I verified through SQL that these are the correct numbers that I need. I wanted to take off the time, so I transformed the data and took the time out. The problem is that when I do this, it messes up my numbers. 

villasenorbritt_1-1653397825219.png

Does anyone know what I could be doing wrong? Is it a relationship issue possibly? Thanks in advance for any help you may be able to provide. 

1 ACCEPTED SOLUTION

Yes, it's recommended to use a calendar table for that and you should start with a basic one and grow with the needs. Keeping the model cleaner is always important!

 

If you allow me i can also recommend to use the tool Bravo from SQLBI to create the date table. It's simple and it allows some customization to start.

 

Bravo for Power BI by SQLBI

 

If you want you can also use a calendar created in Power Query, there are some good example in the web.

 

Tell me if you need any more help!

 

Regards,

Sérgio Silva 

 
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

11 REPLIES 11
SergioSilvaPT
Resolver IV
Resolver IV

Glad i could help! 😊

SergioSilvaPT
Resolver IV
Resolver IV

Hi @villasenorbritt ,

 

Can you show the transformations that you did?

Do you have more than one value for each day?

Are you using a Calendar Table connected to that table? 

I copied the colum and hit the "Date only" option. There are many values for each day. I am currently working on getting a Calender Table in there so that might be the issue. I'm just not sure how to decide which calendar format to choose and if it needs to be aligned with my data source or if I can just stick a generic one in there. Thanks for helping me out. 

Yes, it's recommended to use a calendar table for that and you should start with a basic one and grow with the needs. Keeping the model cleaner is always important!

 

If you allow me i can also recommend to use the tool Bravo from SQLBI to create the date table. It's simple and it allows some customization to start.

 

Bravo for Power BI by SQLBI

 

If you want you can also use a calendar created in Power Query, there are some good example in the web.

 

Tell me if you need any more help!

 

Regards,

Sérgio Silva 

 
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

@SergioSilvaPT   I ended up creating a date table. I am just not sure how to get it to interact with my other tables. I tried making a relationship with no luck. I'm really wanting any holes in my data to show. For example, if the plant is shut down for the day, I'd still like to see that date on my chart and right now it skips over those days. 

villasenorbritt_0-1653410495859.png

 

@villasenorbritt , you will have to connect the Calendar[Date] to the date on your Table[ShiftDate].

 

Something like this:

SergioSilvaPT_0-1653410983954.png

Here i connected Date with Sales Date.

 

Then, in the visuals you must use the columns on the Calendar to create date filters, you should even hide the shift date column on the table, as you can see on my sales table. At the end of each name it should appear an icon like an eye and if you click it it hides the column in report view.

 

Also you should create a measure for your Number of Samples like this:

Nº Samples = SUM(Table[Number of Samples])

 

You should use this metric on the visuals.

 

If everything is correct it should only show days with data.

 

I hope it helps!

 

Regards,

Sérgio Silva 

 
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

 

I am wanting to also show days with NO data as well. Sorry if I worded it weird. 

No problem. That's easy, in the visual in the date value that you will use for example in the x-axis, you can see there is a down arrow. Click there and select an option that says something like Show items with no data.

 

Let me know if you need further help.

@SergioSilvaPT That did show all of the dates, but it starts when my calendar table date starts, not the dates that my data is supposed to be showing. Any suggestions? 

@villasenorbritt did you check the this option? (Show items with no data)

SergioSilvaPT_0-1653497872733.png

 

It shouldn't be enabled.

 

If this didn't help can you post the PBIX file so i can check?

 

Thanks a bunch! I'm going to work on getting a Calendar Table and I will check out the Bravo tool.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors