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 helpDate slicer not working (IF value exists on calendar date)
Hi everyone, i need help editing or creating a new measure that evaluates whether a value in on table exists at a particular date within the calender table . I am working with Two tables, on main table that has a customer and a purchase date. The second table is just a table showing the daily calendar dates. the outcome wanted is to evaluate whether a purchase was made by a customer at a certain date on the daily calender.(True,false). and also include their "purchase point" (person which they purchased from). biggest problem: I have implimented a measure to do the above, however when place the measure into a visualization, my date slicer seems to fail in slicing the visualization by date . the measure is as follows: measure = var _cust = MAX(purchase table[customer]) var _date = MAX('calendar table'[Date]) var _temp = CROSSJOIN(SELECTCOLUMNS({_cust},"cust",[Value]),SELECTCOLUMNS({_date},"dt",[Value])) var _date2 = CALCULATE(MAX(purchase table [purchse_date]),TREATAS(_temp,t1[customer], purchase table [purchse_date])) return IF(_date2=BLANK(),"false","true") the relatioinship: the tables are as shown below : The resulting visualization table : this is the table i show in the final visualisation the measure used in this resulting table is: Help needed If anyone could assist me with writing a measure that could make my requirements work so that my date slicer can work with my resulting table id really appreciate it suggestions are welcome:Date Table - DAX - CALENDAR() - ADDCOLUMNS() - Quarter Issue
Hi, I create a new Date table with DAX using the CALENDAR() function. Adding new columns, no problem. When I add a new "Quarter" colum into the table my table stops starting from Jan 1st and move to start from Jul 1st. I can't understand why. Any help greatly appreciated. First Variant (correct) Date = VAR MinDate = DATE(2016,1,1) VAR MaxDate = DATE(2021,12,31) RETURN ADDCOLUMNS ( CALENDAR(MinDate, MaxDate), "Calendar Year", "CY " & YEAR ([Date]) ) Result Second Variant (wrong) Date = VAR MinDate = DATE(2016,1,1) VAR MaxDate = DATE(2021,12,31) RETURN ADDCOLUMNS ( CALENDAR(MinDate, MaxDate), "Calendar Year", "CY " & YEAR ([Date]), "Calendar Quarter", "CQ " & QUARTER([Date]) ) Result Where I am wrong? Thanks
