Using IF Statement on Date-Times Columns to calculate based on Three conditions
I want to use an IF statement to create three conditions based on appointments for bookings. I have three columns of times. Created Datetime, Failture datetime and Succeed Date Time. The calculation must be relative and take account of the actual date of today. The three conditions... 1. If succeed datetime is blank and the failure datetime is prior todays date (22/01/2020) then the condition is "Fail" 2. if the failture date is ahead of todays date and the succeed datetime is blank then condtion is "Inside SLA" 3. IF the succeed column has a date that is before the failture column then "Pass" All of these work with the current function SLA = IF(SLA[Faildate] < today() || SLA[Faildate] < SLA[Succeed], "Fail", IF(ISBLANK(SLA[Succeed]&&(SLA[Fail]>Today())), "Inside" SLA","Pass")) The issue is that the equation doesnt take into account these Cases. These should be passes because the succeed date is eariler than the fail date. Anyway around this?
Count distinct families across multiple program fact tables—unique columns break the usual patterns
Hi, first-time poster 👋 Goal How can I count distinct ACT_ID (families) across multiple program fact tables, while letting users slice by shared dimensions (Year, State) and program-specific attributes (e.g. MomsMeet[Location]) At the core we're counting how many distinct ACT_IDs there are, filtered by the columns in the various programs. Background: We’re a non-profit with many programs; each program stores the participants in its own fact table. New columns are added to each program and new programs are added, limits common workarounds (like aggregations). Goal: A model that'll enable counting how many distinct families participate across all of our programs. With slicers to filter only families in some programs, or only those those that equal certain values in columns unique to that program (like MomsMeet[Location]). See image. Simplified model Dimensions • Dim Accounts (ACT_ID, State) this is the family table • Dim Individuals (IND_ID, ACT_ID, Name, Gender) • Dim Programs (Program_ID) • Dim Years (Year) Facts (few examples) • Fact MomsMeet (IND_ID, Year, Program_ID, Location, …) • Fact SummerYouthGroups (IND_ID, Year, Program_ID, Session...) (each fact is unique on IND_ID + Year; largest ≈ 60 k rows, this may help a lot) What I’m after • Modelling pattern that preserves program-specific slicers. • Avoid heavy DAX (like USERELATIONSHIP , CROSSFILTER, TREATAS per slicer) if possible Onedrive .pbix file with synthetic data and program names. Or see model diagram below. Thanks for any pointers! 😀 I'll write in a comment which approaches I tried but didn't work.
Power BI model for Production Process
Hi, I have a model for production process, the problem I have is that I have the fact table with transactional sales have columns of [Item,Category,Date,Quantity,Quantity in bottle,Price per KG) and dimension table of calender table and store table and these dimension table has no duplicate values , but there is another dimension table which is product table (this table has 4 column of [Item,Material,Quantity,Price of Material] t=but this dimension table has duplicate items becasue each item contain many materials so every item is duplicated and some materials are are common between some items also, now I want to create relationship between the product table and sales table to create measure to calculate 1-the total cost of each item 2-total materials used every month in separate visual (the quantity of product in sales table and the quanity of each material in material table) RegardsNeed help for a visualization and associated data model
Hi all, I am currently working on a time series data with the following structure: Timestamp - ID - EVENT-TYPE EVENT-TYPE is a column used to track entries and exits and is mapped to a column with +1/-1 as values, with +1 for entry and -1 for exit. Timestamps are Hourly, Non-Unique and Irregular (There can be multiple entries and exits in an hour, and no timestamps for time periods with zero footfall, usually on weekends and during night time) I am currently using a cumulative sum measure on this EVENT-TYPE column to visualize the total occupancy at any given time,and it's working fine. But I also want to allow my users to select any subset of the ID's and shift their in/out times by a certain amount and then see what impact it has on the overall occupancy. I am at my wit's end on how to figure this out, especially as I am not that experienced with the whole DAX/M/PowerBI ecosystem and am really struggling on how to do this. I would really appreciate if someone could show me how to do this in PowerBI. Please also let me know If I have missed any crucial details, or If I am asking this in the wrong section.Query Resources Issue - Actual vs Budget Finance Report
I am trying to construct a matrix that displays my details as rows, presenting five years of data that includes both actual values and budget figures. Specifically, I have five years of actual data and budget values only for the current year, along with calculations for variances between current year (CY) and last year (LY) actuals, as well as the growth from CY actual to LY actual and budget performance. However, I'm facing a challenge because I lack a dedicated column for these particulars; instead, the data is sourced from various MIS columns across three different tables, with inconsistent naming conventions. I can create the visual representation, but it breaks when I attempt to filter the months using a slicer.
Target achievement based of monthly target and family product
Hello all, Please I am new and I need to create a target achievement in values as well in % for the team that should sum the daily revenues based on the family product name: Below the target as shown based on family based on daily input I need to calculate the reached value/family as well in %. In the below table "Customer Profile" there is the daily input of the sales activity pipeline. I have the account name (Created by email) as well , total monthly fee based on products. In the below table is the target values based on family products I have integrated as below: Thank you!
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
