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
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
