Complete date series
Hi a Newcomer to DAX and PBI here, i want to "complete" a date series in my dataset. The following scenario: I feed a data cube from the server into my PBI desktop application. However, this cube only contains date entries for days with values; no value and the day does not even appear. Example: The columns "Dim_company", "Dim_division", "Dim_kpi", "Dim_version" are group variables, each of these groups tracks its own time series. The snippet now shows the example company "1234" with its divisions "ABC" and "DFG". The problem now is that for the division "DFG" no value was delivered on October 2nd 2021 and the line is therefore missing. What I would like to do now using DAX is to automatically insert a blank row for the missing date, so that in my example the two divisions cover the same days. ( The columns have shifted compared to the first screenshot, but I hope you still get what I mean ) For those of you who are also familiar with R - In R I would solve the problem like this: library(dplyr) library(tidyverse) data %>% group_by(Dim_company,Dim_currency,Dim_division,Dim_kpi,Dim_version,Dim_wildcard_dc) %>% complete(Dim_period_daily2 = seq.Date(min(Dim_period_daily2), max(Dim_period_daily2), by = "day")) %>% ungroup()
Displaying same period last month from timestamp column not connected to the date table
I have a model contaning energy measurements on a 15-minute timestamp level, date dimension and time dimension. What I am want to do is displaying the values in this fashion: The page is sliced by month from the date dimension, but the timestamp values on the x-axis here is from the fact table. The problem is that I also want to show last months values on top of this month values. But when I use formulas such as parallelperiod(), dateinperiod(), or dateadd() to get last months values, timestamp values will show for that parallelperiod. Any tricks to manage this? Link to .pbix: https://www.dropbox.com/s/ewh6a027fiuou75/Model.pbix?dl=0 Thanks!
finding dates for weekday in the future
in our business we have a feature that lets users book a timeslot and weekday to recieve their service , for example ,user X booked his service to be every Friday 10:00 AM , user Y has booked his service to be every Tuesday 1:00 PM So user X will recieve his service every Friday 10:00AM and user Y will recieve his serive every Tuesday 1:00 PM the request is : create a matrix for every future date , how many users have booked a specific timeslot , ex: tuesady 06-29-2021 Wednesday 06-30-2021 Thursday 07-01-2021 Friday 07-02-2021 Saturday 07-03-2021 Sunday 07-04-2021 Monday 07-05-2021 tuesady 07-06-2021 Wednesday 07-06-2021 Thursday 07-07-2021 Friday 07-08-2021 10:00 AM 1 User 1 User 11:00 AM 12:00 PM 1:00 PM 1 User 1 User so as you can see , dates are in the futute , and do not exist in our database , the data i have is : -User ID -weekday -time -service date ( data will be shown once service date has passed , if the date hasnt come yet , data will be NULL) ex for the table : User ID Week day Time Serive date 124 Tusday 6:00 PM 6/27/2021 567 Friday 11:00 AM NULL 566 Sunday 4:00 PM NULL 433 Friday 3:00 PM NULL so how can i find for each weekday and time the date for it in the future evey month ?Calculate running elapsed time for each record usingDAX Measure. Also Aggregate the same.
Hello, Need some help with DAX measure to calculate running elapsed time in hours for each records since its created timestamp. The ask is two fold. * First one is when this measure is added to a table, it should show elapsed time in hours for each row as of Now. * Second one is when this measure is added to a card visual, it should show average elapsed time in hours for all applicable rows as filtered. Data Table: The Elapsed Time calculation should be in hours "createdon - Now()" and should keep updating when the visual is updated using the current date/time (Now()). If this same Measure is used in, lets say "Card Visual", it should show Average of elapsed time for all the records. Appreciate any help on this.Date issues on a custom date
Hello, I have issues to associate dates correctly. I don't really know how to workaround this. Note: the second table is a view and i can modify it for my needs. I have dimension date table as follow: I have an other table with products and dates like this (i show you only dates here): I made a relation between them: I want to count customer by entity for a specific report period, so i made a segment on the Generic_Period. I created mesures for this segment: Customers = CALCULATE(DISTINCTCOUNT('Customers'[Customer]), filter(Customers, Customers[Generic_Period] = DATEADD(Customers[Generic_Period],0,MONTH))) Customers M-1 = CALCULATE(DISTINCTCOUNT('Customers'[Customer]), filter(Customers, Customers[Generic_Period] = DATEADD(Customers[Generic_Period],-1,MONTH))) The first probleme is Q-1 returns 0 values : The second problem, It doesn't work if the day number isn't the same as previous month. If i select 30/09/2019 it will not retrieve the 31/10/2019 even with end of month -1 like this : ENDOFMONTH(DATEADD(Subscription_Partner_by_month[Generic_Period],-1,MONTH)) Thanks a lot for your time.
Find MAX Date from previous records with the same ID
Hello everyone, I could need some help wiht the DAX formular for a new calculated table column. My goal is to get the last datetime for a specific filter within the same table. My query is working fine with a small amount of data but it quickly runs into performance issues (OOM & Time). For a better understanding of my goal you will find example data, the expected output and my current query below: Given Table: TaskID Start DateTime End DateTime ... ... ... 222 19.02.2020 06:54:47 19.02.2020 06:57:09 222 19.02.2020 06:57:09 19.02.2020 07:01:12 333 19.02.2020 06:58:52 19.02.2020 07:02:00 222 19.02.2020 07:05:15 19.02.2020 07:10:46 444 19.02.2020 07:12:41 19.02.2020 08:11:57 222 19.02.2020 07:13:33 19.02.2020 07:17:14 444 19.02.2020 08:11:59 19.02.2020 08:16:42 ... ... ... Expected Output for the new column "Previous EndTime": TaskID Start DateTime End DateTime Previous End DateTime ... ... ... ... 222 19.02.2020 06:54:47 19.02.2020 06:57:09 19.02.2020 06:53:23 222 19.02.2020 06:57:09 19.02.2020 07:01:12 19.02.2020 06:57:09 333 19.02.2020 06:58:52 19.02.2020 11:23:48 18.02.2020 17:52:45 222 19.02.2020 07:05:15 19.02.2020 07:10:46 19.02.2020 07:01:12 444 19.02.2020 07:12:41 19.02.2020 08:11:57 19.02.2020 06:32:11 222 19.02.2020 07:13:33 19.02.2020 07:17:14 19.02.2020 07:10:46 444 19.02.2020 08:11:59 19.02.2020 08:16:42 19.02.2020 08:11:57 ... ... ... ... Currently in use DAX: Previous End DateTime = CALCULATE( MAX('Table'[End DateTime]), FILTER( 'Table', 'Table'[TaskID] = EARLIER(TaskID) && 'Table'[End DateTime] <= EARLIER('Table'[Start DateTime]) ) ) Additional Information: The "Previous DateTime" has to be from the same TaskID. The "Start DateTime" does not have to be equivilant to the "Previous End DateTime". The gap between "Start DateTime" and "Previous End DateTime" can be 0sec but it could also be a gap of more than a month. For the column "Previous EndTime" i can't use a measure because it shouldn't be affected by any additional filters and I have to do further calculations (e.g. difference between "Previous End DateTime" and "Start DateTime"). I'm pretty new to PowerBI and DAX, so I would be happy and thankful for any information, help, advices or a new perspectiv to solve this problem.Specific Date range for projects
Hello! I hop my explanation does this justice. I am doing a Cost Savings Projects analysis. It is a manufacturing plant, and we are changing aspects of the production to lower cost. For example, Project 101 may be changing a bag type and Project 102 may be changing labels. Each project starts a different day and runs for 52 weeks. so one project may be 3/1/2020-3/1/2021 and the other will be 6/2/2020-6/2/2020. What I need to be able to do is associate the Project ID with its product SAP and then have it only pull that project during the 52 weeks that the project is running, and anything outside of that specific range to be 0 or blank. But I have only been able to filter using the date filter, but i need it to filter specifically to the Project ID itself since they all have different savings goals and date ranges. Thank you !!!!Measure: show dcount as % by date hierarchy
Hey everyone, i've just started in power bi and im having trouble with some calculations, for starters i just nee to calculate and graph the On Time KPI foe my company deliveries. I have scheduled delivery date and time with low and high limits as first set of data, an then i have the real delivery date and time, everything identified by a unique delivery ID. I basically need to calculate compliance % in 3 levels (Early, on time, and late) and graph them by date hierarchy. My data structure is as follows: Guide Min Time of Arrival Max Time of Arrival Arrival Time On Time On Time (groups) 12345 11/11/2020 06:00 11/11/2020 07:00 11/11/2020 05:00 Early On Time 23456 11/11/2020 06:00 11/11/2020 07:00 11/11/2020 06:30 On Time On Time 34567 11/11/2020 06:00 11/11/2020 07:00 11/11/2020 14:00 Late Late On Time - Column is the result of the following calculation: (which i added in Power Query using M while transfoirming my data) = Table.AddColumn(#"Extract - Hora Creación", "On Time", each if [Tiempo Llegada] < [Tiempo Min Entrega] then "Anticipado" else if [Tiempo Llegada] > [Tiempo Max Entrega] then "Retraso" else "On Time") On Time (groups) - just groups early and on time for practical purposes my company considers both as acceptable, so if the sum of both is greater than 70% everything is ok. I tried the following dax formula for the measure im trying to graph and it does. On Time KPI = CALCULATE(COUNT(Beetrack[On Time]),ALLEXCEPT(Beetrack,Beetrack[Tiempo min entrega].[Día],Beetrack[On Time])) / CALCULATE(COUNT(Beetrack[On Time]),ALLEXCEPT(Beetrack,Beetrack[Tiempo min entrega].[Día])) The problem here is the resulting graphs cant be affected by any filter, and cant be drilled down/up by any date hierarchies, so i cant show monthly nor annual nor weekly values. I also cant highligt the same data when i click on other graphs in the dashboard to segment the data. Is there any other way to achieve the same calculation dynamically? so i can get the values by date hierarchies? and that the graph is not always static?Need help writing a DAX formula showing if one column is >= another, it shows as late
Hello everyone! I'm fairly new to Power BI and DAX, and my google searching hasn't been very successful. I have these two columns that I need to write a DAX measure for. The Arrival shows when a driver got to his pick up location, and the depart is when he left. I need to write a formula showing that if a driver left 45 minutes or after his arrival to the pick up, it is shows as late. Thank you in advance, and I look forward to learning something!Filter month year column to exclude past dates from slicer
I have a dates key table that runs from Jan 2018 - Jan 2021. I have a month year column (Text Format) so it doesn't display data past today: On or Before Today = 'DimDate'[DateS] <= EOMONTH(TODAY(),0) Is there a way to have a slicer's past dates respected when it changes? e.g. When I slice the last 24 months I get this However when I slice the last 2 months I still get the begining dates of slicer: Something along the lines of this??? On or Before Today = ALLSELECTED('DimDate Sort'[Dates]) >= 'DimDate'[DateS] <= EOMONTH(TODAY(),0) Thanks in advance *CAVEAT - I have a DimDate table for counts and a DimDate Sort to build relationships off in case you're wondering
