Forum Discussion
Diffrenet tables one Date filter
I dont have a date and time colum in my tables , and I prefer not to add columns to my tables(Since I already have a dateeith time column in them)
So instead I used the similare option you guys gave earlier in the thread
Just instead of using Calander() I created a table as follow(using kusto get data):
let _stepSize = 1m;let _offset = 1d;let _startTime = startofmonth(now(), -1) - _offset;let _endTime = bin(now(), 1m) - _offset;range Timestamp from _startTime to _endTime step _stepSize| extend Timestamp = Timestamp + _offset| extend WeekOffset = toint((startofweek(Timestamp) - startofweek(now())) / 7d)| extend Timestamp = Timestamp - _offset| order by Timestamp descand then I added the realations to them and it worked.Thanks for helping ,
Super UserI would suggest two table one for date and one for time . For time refer :https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
Have date column without the time and join the dates with the date calendar table
Date = date[Datetime].date
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Super UserAnonymous , Does the above replies helps. if you need more help make me @
Appreciate your Kudos.
Hello amitchandak
unfortuantly it didnt work , I think the problem is that my Date table i created using CALENDERAUTO()
is without time , while my other 2 tables are with time.
Any chance that thisis the problem , and would anyway like to know how to create a slicerwith time as well.
Thanks.
- amitchandak
Anonymous
You should create a date column from datetime like give below
date = [Datetime].date
Or
Date = date(year( [Datetime]),month( [Datetime]),day( [Datetime]))
And join that with the calendar.
If required you can create hr dimension or time dimension
https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
Hey amitchandak
I dont understand what do you mean , can youplease alobrate as in what should I do .
for now what I did was creating the following table
{TimeTable =VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0), (23)), "Hour", [Value])VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Second", [Value])RETURNADDCOLUMNS(CROSSJOIN(HourTable, MinuteTable, SecondsTable),"Time", TIME([Hour], [Minute], [Second])) }Now I have 2 tables , one calender and the other one timtableso I am not sure what do you mean hen saying join it with calander , how do I join them into one field ?and make the relation to the other tables?