Suming sales between dates from one table and filters from another
HI PBI community, I have an ask to calcuclate the total sales for stores in an event promotion, and I'm having trouble thinking of an appropriate way to use Calculate since I need values from three tables The first table is a list of stores and the their sales per day like below store_num salestransaction_date sales 3045 4/1/2023 0:00 39909.38 12233 4/2/2023 0:00 27800.62 12000 4/3/2023 0:00 14799.82 33 4/4/2023 0:00 5598.49 8229 4/5/2023 0:00 2113.54 16939 4/6/2024 0:00 2894.86 11267 4/7/2024 0:00 2081.68 16339 4/8/2024 0:00 10574.59 17568 4/9/2024 0:00 6834.87 16810 4/10/2024 0:00 6049.49 The event period information comes from a separate file. I don't necessarily need the "this year or last year" column but I will need to later do YOY sales for each month if that helps. This Year or Last Year Month start end Event Nm TY APR 4/19/2024 4/25/2024 Event C LY APR 4/12/2023 4/15/2023 Event D Finally, there is a seprate list for whether teh store particpated in the event or not based on whether or not they bought a sign kit for the month Month STORE SIGN KIT APR 3045 MINI APR 12233 MINI APR 12000 MINI APR 33 MINI APR 8229 FULL APR 16939 FULL APR 11267 FULL APR 16339 FULL APR 17568 FULL MAY 16810 FULL So I need a connection from the sales transaction date to the promotion dates, and a connection for the Month columns, and a connection for the store_num columns. This makes my data model a little complicated and not sure how I could find sales form that first table between the start and end dates, as well as identifying if they participated. I tried the model below using a date table as a "bridge", but nothing is being filtered - i think there's too many relationships. My deisred result would something like below This Year or Last Year Month start end Event Nm sign kit store count sales TY APR 4/19/2024 4/25/2024 Event C MINI 5 100 TY APR 4/19/2024 4/25/2024 Event C FULL 6 200 LY APR 4/12/2023 4/15/2023 Event D MINI 7 300 LY APR 4/12/2023 4/15/2023 Event D FULL 8 400
How to make a segmentation data data filter with data in datetime format?
Hi! I'm from Brazil and totally new to Power BI, so sorry in advance for any English mistakes or innacurate terminology. I hope I am able to make you understand the problem I'm facing. I am trying to add a segmentation data filter to my dashboard, like this one: I have a main fact table containig data from a set of telephone calls, from the project I'm working on. One of its columns is called 'data_hora', which contains the date and hour the calls were made in datetime format (dd/mm/yyyy hh:nn). It looks like this: I need to make the segmentation data filter look like this, so the user is able to select both date and time: However, when I try to add this visual and drop the data_hora column to the data field, selecting the style 'Between', the time information never shows up in the filter: > Options > Styles > 'Between' (Entre) What do I need to do in order to make the time information also be selectable along with the date?
Pass slicer date range to a variable
I am having trouble passing a slicer date range to a variable. I have two tables: 1. Before_Day_Key is used for my before slicer 2. After_Day_Key is used for my after slicer I have created variables that create the min and mix date for each of the slicers. Now I want to create a variable that has the date range of each slicer. What I am expecting is: VAR _periodBefore: 01/17/2023 - 03/20/2023 VAR _periodAfter: 03/28/2023 - 05/29/2023 Before_Day_Key table is here. After_Day_Key table is here. Can you help complete the statement: PED2 = VAR _minDateBefore = MIN('Before_Day_Key'[Business Date]) VAR _maxDateBefore = MAX('Before_Day_Key'[Business Date]) VAR _minDateAfter = MIN('After_Day_Key'[Business Date]) VAR _maxDateAfter = MAX('After_Day_Key'[Business Date]) VAR _periodBefore = VAR _periodAfter =
Visual filter not working for date field slicer when using Before, After, or Between?
I have a measure that just checks if the slicer above isfiltered (Is Terminated) and returns 1/0 You can see there appears to be no values when I am using the Between setting If I change the slicer to dropdown, the values show up.
Help with calculating specific delay time per day
Hi, I am trying to make a report that would give the delay time per day (which would give me the run time per day to get approximate TPH). An example delay data table is formatted as shown below. Since I have to calculate tons per hour for every day using another table with production information, I created a Calendar table (just a one-column table with only the dates using the CALENDAR() function) that is connected with both the delay table and production table. However, I am struggling to calculate the number of hours of delay time per day with DelayType 0 as a column in the calendar table with the format shown below using the previous delay table as an example. I tried making a calculated column using DATEDIFF() but I am struggling to get only the entries with DelayType of 0 as well as how to deal with delays that span for multiple days (For example, 05/26/2023 falls within the 4th entry of the delay table so the total delay time for that day would be 24 hours). I would appreciate any help or guidance on what I can do to get the final formatted Calendar table. Thank you.Finding customer status for a given date
Hi Masters, Given is a table of status changes of customers (Old Status, New Status, Change Date, Previous Change Date) as described: Also given is the column "First Purchase Date" which is constant for each Customer ID. I wish to add a calculated column which will return the customer status of each customer while his first purchase. in the example above, customer 123 first purchase date is 15/01/2022. therefore, according to his status changes dates his status while first purchase was "b" (15/1/2022 is between 01/01/2022 and 01/02/2022) thanks for helping out. Amit
