Dating Advice from a Geek: Date Table Why & How
Learn more and register to get the Teams link through Meetup: https://bit.ly/november-community-meeting-event-page We're so excited to host our first meeting as the Minnesota Fabric/Power BI Community! Agenda for this community meeting: - Introductions - Tell us who you are and how long you've been using Fabric/Power BI - Presentation from Brian Larson - Dating Advice from a Geek: Why to Use a Date Table & the Best Way to Create One - General Discussion - Any pain points anyone is needing support with currently? More about the presentation: Having a Date table (a table dedicated to calendar dates) is not required in Power BI, but it provides a number of very powerful benefits. A Date table unlocks functions that make it easy to calculate metrics like Year-over-Year Growth or Quarter-to-Date Expenses. It can also make selecting date ranges like "Last Completed Month" and "Rolling 12 Weeks," as well as defaulting a report to one of these time periods much easier. We'll demonstrate these advantages and walk through a quick and easy method to create a robust Date table in your own Power BI reports. Source code will be shared!Active per month calculation with disconnected date slicer
Hi all, I've tried searching around and discovered this is a fairly common problem with various solutions, though I cant seem to implement one that works. I'm trying to create a visual which will show in a line chart the number of active "connections" in a given month. Our business calls case files or enquiries "connections" - but it's a similar problem to calculating active employees or something like that - we have a start date and an end date for each connection, where a connection is still open the end date will be blank. I have a Date Table, a connections table and another disconnected date slicer table. The principle is the user chooses whatever date range they want, and the measure will calcualte which connections should be active for the chosen range: Filter Connections Worked With = var _rangeStart=FIRSTDATE('SlicerDateTable'[Date]) var _rangeEnd=LASTDATE('SlicerDateTable'[Date]) return IF( SELECTEDVALUE(SQLMain[c1_startdateLT]) <= _rangeEnd && ( SELECTEDVALUE(SQLMain[c1_enddateLT]) >= _rangeStart || ISBLANK(SELECTEDVALUE(SQLMain[c1_enddateLT])) ), 1,0 ) Then this measure is used within a second measure to count the active connections: Count Connections Worked With = CALCULATE( DISTINCTCOUNT(SQLMain[ticketnumber]), FILTER( SQLMain, SQLMain[Filter Connections Worked With] = 1 ) ) + 0 This is fine, but I cannot then work out how to show the number of connections per month on a graph due to lacking a relationship between the table with connections in it (SQLMain) and the Date Table. Naturally I dont want to create a relationship with the date table and just the start date or the end date as this wont properly show on the graph. Other implementations I've seen generally dont use a disconnected date table and measure to filter the thing being counted, so I havent been able to think of a good way to achieve the result. I do need to keep the disconnected date slicer table logic however as a lot of the report is built using this logic. Any help with this would be appreciated!
Help to Solve a Date DAX.
Hi All, I have a Date table issue I would like to resolve in my DAX. I have a w Week filter, that when selecting a week number, would like to sum the Month to Date data. However, if I select Week 14 from my filter list per the table below, I only get data for Week Ending 1/10/2023. But, I need it to sum Weeks 11 to 14 coresponding to Month Name September. My current DAX is below. Much appreciated in advance. Current MTD = CALCULATE(([HD Volume]),DATESMTD(Dates[Week Ending]))
How Do I keep Sales PY Calculation When a Filter is Applied?
Hi! I am an extreme novice and have scratched together some Dashboards in PBI with little to no knowledge of whether or not the way I am going abnout things is best practice. My issue is I have time series data formatted below that goes back 104 weeks by item I have a date table to roll up all of the various data sources I have so they have a consistent week ending date. (Although not neccesary for this dashboard as it only has one data source/retailer) And then I created a Date Filter table so I could use slicers to select 52/26/12 weeks time periods Using these calculations Since there is no column for Sales PY in my data I just want to make a command that finds the sales for the same item/brand etc for the same exact period/week last year and this is what I came up with after failing with SAMEPERIODLASTYEAR It appears to work when I don't have the date filter/slicer applied as shown below However there are a few issues... 1. Since my date table runs into the future it forward calculates the PY sales. I only want my data to go up to the most recent current year date 2. When I apply my date filter slicer to 52 weeks the PY sales dissappear as shown below. I fundamentaly can understand why they would as the filter is only showing data from the last 52 weeks and therefore I would assume it omits going back further to pull those PY dates. I just don't know how to go about fixing this Let me know if any other context is necessary. Thank you!
Own date table is not properly filling itself out
Hi everyone, I am having the problem that my DAX command for creating a date table is not properly working. I am using the following DAX formula: Date = VAR MinYear = 2012 VAR MaxYear = 2031 RETURN ADDCOLUMNS ( FILTER ( CALENDARAUTO( ), AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear ) ), "Year", YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "mmmm" ), "Month Number", MONTH ( [Date] ) ) What I find especially confusing is the fact that this command worked in an earlier dashboard but would not work now. Here is the screenshot of my older dashboard where it worked as intended: and here is a screenshot of how it fails to work: Does anyone know why this is the case and what the remedy to this might be? Thanks in advance!Date Table and Time Intelligence Functions not working anymore
Hello I dont know the reason but i had properly working date table function and time intelligence functions before, but not working anymore. I dont know if its related with latest updates. As you may see in the below screenshot it gives error after Date function at 3rd row. If i dont touch the dax formula its ok but if i F2 + enter all my report will blow up. Same happens to my DATESINPERIOD formula, As you can see below, last parameter is not accepting MONTH as Interval. which was perfectly working before. If i re enter on this formula, again all my report will be broken. Thanks in advance for you help
