How to make a DAX calculation return BLANK rows
I'm working on a Power Bi template for my company. Since we are a global comapny i added Time Zone tables that help the end-user filter the visual for their Time Zone. Now I'm trying to create a DAX measure that will get the values in a DATE/TIME type column and sum the given UTC Offset in Hours, however I'm having a hard time when there's no value in a give row, DAX will just either not return the rows with blank or return the offset. What I'm trying to achieve is a function that will ignore the blank values and still return them, and when there is a value actually SUM the End Time UTC with the Offset. Note: [Current UTC Offset] is a measure that returns a decimal number from -12 to +12 How can i achieve this?
Help with sales measure between two dates for custom calendar
Hi, I know this is a common ask but I'm stuck on this Dax measure. Basically, I need to calculate the sales variance betwen two dates in my date table for any slice/dimension my user needs. I'm used to using the dateadd or sameperiodlastyear functions for this, but I'm stuck since I'm using custom dates for our new fiscal year calendar. My sales table comes in weekly with a week start column, and a list of stores and products For my date table, I added a "this year" and 'last year" column for the comparison dates. Corpweekstart is connected to my date in this table. I tried the below DAX for YOY sales which works perfeclt in a weekly view. But it appears blank when i look at it in total or as years. From what I've read, it's because of SELECTEDVALUE() and I think i need to use HASONEVALUE() or ISINSCOPE() instead? But I'm not sure if that's correct or what field to use it with? VAR ty = SUM(Sales_Agg[sales]) VAR PrevWeekStart = SELECTEDVALUE ( 'adhoc_ace_date'[ly_week_start] ) VAR ly = IF ( NOT ISBLANK ( PrevWeekStart ), CALCULATE ( SUM(Sales_Agg[sales]), TREATAS ( { PrevWeekStart }, 'adhoc_ace_date'[ace_date] ) ), BLANK () ) RETURN IF (ISBLANK( ly ), BLANK (), (ty - ly)) It works fine when I look at it weekly, but not yearly (or any other dimension).
Inflation Rates Adjusting Year of Payout to Loss Year accounting for inflation
Hello, I have been tasked out with creating a dashboard that shows the payout spend compared to the loss year spend, accounting for inflation (year over year, various years). Basically, I want to see if im spending more money today than the loss date. Data and rates below. Payout Date Loss Date (Year) Payout Year Region Total Inflation Rate Year 1-Jan-24 2023 2024 A -1547.72 2.10% 2017 1-Jan-24 2023 2024 B 4040.44 2.40% 2018 1-Jan-24 2023 2024 C 104.2 1.80% 2019 1-Jan-24 2023 2024 A 12231 1.20% 2020 1-Jan-24 2019 2024 B 2561.4 4.70% 2021 1-Jan-24 2023 2024 A 27135.18 8.00% 2022 1-Jan-24 2022 2024 B -12285.27 4.10% 2023 1-Jan-24 2023 2024 A 315123 0.00% 2024 1-Jan-24 2014 2024 B 407 1-Jan-24 2017 2024 A 200206.6 1-Feb-24 2023 2024 B 749.47 1-Feb-24 2023 2024 C 22837.77 1-Feb-24 2017 2024 A 7 1-Feb-24 2023 2024 B 15403.35 1-Feb-24 2024 2024 C 165 1-Feb-24 2023 2024 A 406.98 1-Feb-24 2022 2024 B 31.35 1-Feb-24 2016 2024 C 1986 1-Feb-24 2023 2024 A 7497.12 1-Feb-24 2023 2024 B 12342.62 1-Feb-24 2015 2024 C 4964.75 1-Apr-23 2014 2023 A 3161.5 1-Apr-23 2017 2023 B 6064.65 1-Apr-23 2019 2023 C 492.27 1-Apr-23 2017 2023 C 2628 1-Apr-23 2023 2023 C 72 1-Apr-23 2023 2023 A 4952.46 1-Apr-23 2015 2023 B 200.48 1-May-23 2023 2023 C 68621.64Calculate LTIFR & TRIFR for multiple companies
Hi Community, I need to be able to show on a dashboard the calculated LTIFR (Lost Time Injury Frequency Rate) & TRIFR (Total Recordable Injury Frequency Rate) rate for my own company as well as those of our subcontractors. My company is represented as 'JKL4' and needs to be separate; all other companies should have a combined rate. I also need this to be on a rolling 12-month basis, so the rate for Jan 2024 will take into account the hours worked and injuries recorded from Feb-23 to Jan-24, the Feb-24 will be from Mar-23 to Feb-24 and so on and so forth The data is coming from an online SharePoint list that has a lot of columns, but I only need to use six for the calculation Month Contractor Hours LTI (Lost Time Injury) RDI (Restriced Duty Injury) MTI (Medical Treatment Injury) LTIFR calculation (LTI only) TRIFR calculation (LTI, RDI, and MTI) I have included a link (link no longer available) the data I will be using. Only the contractor names have been changed; all other data is correct The parent table name is 'Tb_HSEQ_Stats'
Need a Dax Measure to count the number of students are passed (should pass all the subjects)
Hi Experts, I'm working on a Power BI report where the requirement is to calculate the total number of students who have passed all their subjects within a selected date range. A student is considered as "Passed" only if they have passed all subjects—even if one subject is failed, the student should be treated as "Failed". I have a working DAX measure that gives the correct result when only today's date is selected. However, when a date range is selected using the slicer (e.g., yesterday + today), it does not return the correct count. It seems to only consider part of the range or just one date. lbendlin Total_Students_Passed := CALCULATE( DISTINCTCOUNT('Sheet1'[Student]), FILTER( VALUES('Sheet1'[Student]), CALCULATE( COUNTROWS( FILTER( 'Sheet1', 'Sheet1'[Exam_Result] = "Fail" ) ) ) = 0 ) )Dax Formula for Graph Multiple Series with Variables from User Inputs
I have a report user that wants me to create a graph where the data points are based on inputs he can enter (using slicers pick a product, a date range, a system, etc. There are at least six different entries in total) and then put a second set on the same chart. The example in excel is he creates series based on different inputted information and then creates a graph that compares the two. Is there a way to do this with dax so it's based on user inputs through slicers but they don't have to create the graph? Here is his example that he creates in excel for his reporting. The batches are not defined in our system, the engineers and production just know based on date range which items belong in which batch, so they aren't something I can control or easily define. The user (engineers/production team) need to be able to define each series for the graph in the power bi report.
