Calculated Column Static Output once Criteria Hit and Doesn't Reverse
Hi All, I'm trying to add a column to a Summary Table that shows "Lease-up" until a certain criteria is hit, then it changes to "Stabilised" but once it shows "Stabilised" it doesn't change back to "Lease-up" if it then falls out of the initial criteria. The criteria is for the change is: - Released Count = Property Count - Occupancy >= 90% Once the criteria is hit, the Status should show as "Stabilised" but if the data then changes and falls out of the initially hit criteria, once it sows as "Stabilised" it doesn't revert back to "Lease-up" I've tried using a "First hit date" column but this changes week to week when the data updates in to pbi so this doesn't work - maybe I need a seperate date table to reference? but I'm really stuck on thisIssue with Calculated table with User's Selection
I have an excel table that looks like this: Financial Year VAT Number Sector Net Sales EBITDA Inventories 2019 100 1 10000 9000 800 2019 200 2 12000 9500 900 2020 100 1 15000 10000 1000 2020 200 2 16000 10000 1000 2021 100 1 17000 13000 450 2021 200 2 18000 14000 1200 2022 100 1 19000 15000 800 2022 200 2 20000 16000 800 2023 100 1 21000 17000 1200 2023 200 2 22000 18000 1200 ***I have a lot more columns related to the companies' balance sheet items, however I am simplifying it in this example. I imported the data to PowerBI and inserted a matrix which shows the following: Financial Year, Sum of Net Sales, Sum of EBITDA, Sum of Inventories, etc. I added 2 slicers: one that filters the years that appear in my matrix ('Sheet1'[Financial Year]), and one that filter the sector ('Sheet1'[Sector]). I created a calculated table, which contains one column with the VATs that appear in a Common Sample. This Common Sample refers to VATs who appear both in 2022 AND 2023, and have non-zero and non-blank Net Sales in both of those years. If a VAT does not meet this criteria, its value is blank in the calculated table. This table is connected to the original table (Sheet1) via a One-to-many relationship that links 'Net Sales CS 2022-2023'[VALID NET SALESVAT] to 'Sheet1'[VAT Number]. I input this calculated table in a slicer, selected all values apart from the blank ones, and this correctly filters my matrix and shows the Sum of Net Sales, EBITDA, etc, for the filtered common sample. The type of this table is the following: Net Sales CS 2022-2023 = VAR VATs2022 = FILTER( ALL('Sheet1'), 'Sheet1'[Financial Year] = 2022 && NOT(ISBLANK('Sheet1'[Net Sales])) && 'Sheet1'[Net Sales] <> 0 ) VAR VATs2023 = FILTER( ALL('Sheet1'), 'Sheet1'[Financial Year] = 2023 && NOT(ISBLANK('Sheet1'[Net Sales])) && 'Sheet1'[Net Sales] <> 0 ) RETURN DISTINCT( INTERSECT( SELECTCOLUMNS(VATs2022, "VALID NET SALES VAT", 'Sheet1'[VAT Number]), SELECTCOLUMNS(VATs2023, "VALID NET SALES VAT", 'Sheet1'[VAT Number]) ) ) I need help with the following query: Instead of creating a calculated table for the common sample of 2022 and 2023, I would like a calculated table with a dynamic selection of years of Common Sample, based on the users' selection. For example, a user might select the Net Sales Common Sample between 2020, 2021, and 2022. This calculated table (or column, or measure) will be added in a slicer, so that I can filter my matrix and showcase the Sum of Net Sales, EBITDA, Inventories, etc, ONLY FOR the Common Sample. IMPORTANT: I don't want to create separate measures for each and every column (which means balance sheet item), as there are too many of them in my data. I created a disconnected table that allows the user to select the years for which he/she wants to compute the common sample. I believe that this should be somehow to the calculated table, but not to the original table. YearSelection = DISTINCT('Sheet1'[Financial Year]) However, I didn't have any luck with different tries of creating a calculated table, measure, or column based on my query, SO I WOULD REALLY APPRECIATE YOUR HELP 🙂. EXTRA QUERY: The user might want to select a different column than the Net Sales one to find the Common Sample of the VATs for selected years.
Help with Last Quarter Formula
I calculated the sum for last quarter and last quarter previous year but they only work when I plot them against a time period. I am trying to plot it against customer name instead and the measures just return blanks. My data has sums of activity by quarterly dates only, not daily. These are my measures that are working but only if plotted against date logic. Net Added Last Quarter = CALCULATE(SUM(OWSSummaryByQuarterByEU[channel_licenses_net_added]), PREVIOUSQUARTER(OWSSummaryByQuarterByEu[End of Quarter].[Date])) Net Added Last Quarter Prior Year = CALCULATE([Net Added Last Quarter], SAMEPERIODLASTYEAR(OWSSummaryByQuarterByEU[End of Quarter].[Date])) Does anyone have a formula that would give me the sum of last quarter when not plotted against time? I'm assuming it needs to be a calculated column but everything I try is returning blank or error. Willing to try DAX or Power Query solutions.Calculated Column To Determine Attendance Status
I am building an attendance dashboard and already integrated holidays and weekends and a calculated column that calculated the durantion between the earliest entry and latest exit timestamps and everything checks out except for the "Attendance Status" calculated column where I am using the SWITCH function with all possible conditions but I am not getting the expected results as can be seen in the screenshot, when the day is "Regular" and the completed hours are equal or more than 8 then it should be fulfilled otherwise it is incomplete same with ramadan but the hours being 5 instead but I "Absent" and "Not Required" are the only results I am getting even when that is not the case. I tried to test out each condition individualy to troubleshoot but to no avail. Below is the dax expression I used: Attendance Status = SWITCH( TRUE(), ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"}, "Not Required", ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Regular", "Absent", ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Ramadan", "Absent", RELATED(Calendar[DayType]) = "Regular" && [Completed_Hours] >= 8, "Fulfilled", RELATED(Calendar[DayType]) = "Regular" && [Completed_Hours] < 8, "Incomplete", RELATED(Calendar[DayType]) = "Ramadan" && [Completed_Hours] >= 5, "Fulfilled", RELATED(Calendar[DayType]) = "Ramadan" && [Completed_Hours] < 5, "Incomplete", RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"} && [Completed_Hours] >= 8, "Fulfilled", RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"} && [Completed_Hours] < 8, "Incomplete", "Unknown" )
Display column total on measure value
Hi all, I am new to Power BI and can't seem to display the totals on my matrix for each column. Hoping someone can help! My matrix has (3) columns. The 1st is a quick measure, the 2nd is a custom measure that counts rows from my data source, and the 3rd is a custom calculation. My 1st column shows the totals, however the 2nd and 3rd do not. After doing some research, I believe the issue is due to custom forumlas in the 2nd and 3rd columns. But I've tried many different recommendations from other thread with 0 success. My formulas for the 2nd and 3rd columns are as follows: Count of Completed Training = COUNTROWS(FILTER('Training Tracking', SELECTEDVALUE('Training Tracking'[Training Topic - Levels]) = 'Training Tracking'[Training Topic - Levels] && SELECTEDVALUE('Current Employee Names'[Employee Name]) = 'Training Tracking'[Trainee])) % Complete Training Level = IF([Count of Completed Training]> 1, [Required Training]/1, if(NOT ISBLANK([Count of Completed Training]), [Count of Completed Training]/[Required Training])) Because the "Count of Completed Training" formula counts duplicates (intended). I had to create an IF statement so the "% Complete" didnt exceed 100%. I am not overly concered about the total on "Count of Completed Training", but the "% Complete" is key for the functionality of my report. Also, I've turned the column and row subtotals to "on" in the visulazations, but the apply settings to the per column level is set to "off" and wont allow me to change it (greyed out). Let me know if you require anymore info. Thanks in advance!!
Adding Calculated Column to Date Table based on Date Ranges in Another table
Hi All, I am looking for some help adding a column to my date table based on date start and end found in another table. What I am trying to do is to add a column to my date table which specifies for each date in the table, which semester it falls into. The dates for each semester can be found in the Semester Date table. There is a start and end date for each semester. I have tried to adapt the solution found in another post. The issue I am having is that the column is currently only returning the final semester found in the Semester Date table. The DAX I have is Semester Date = VAR _a = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( MAX( 'Semester Dates'[Academic Semester]), FILTER ( ALL ( 'Semester Dates' ), 'Semester Dates'[Start]>= _a && _a <= 'Semester Dates'[End] ) ) Date Table; Semester Date Table; Any help would be greatly appreciated. Many thanks
% calculation per row
Hello I'm trying to create a calculate column of cost % out of total cost with filters, attaching the pic below for example I have two column that filtering my calc, the right column (cost %) is what im trying to create, please note that I have a lot of month and products so creating a measure oer each one is not relevent. thx again !URGENT help on DAX calculated column for different cases to display in power bi
Hi All, Please find the below screenshot of sample data, need help on the different cases reasons for the below data to display in different column reasons. Please help it's very urgent. I have tried different approches but unable to reach solutions. Below are the requirements need to work on the DAX calculated column need to display in different columns: CASE 1: "Two SCIDs merged into a confirmed SCID" CASE 2: "Confirmed SCID has been Unmerged" CASE 3: "Insured Info mismatch" CASE 1: (When two different policies were assigned to two different SCID_POST (000 and 001), but under NSCID both SCID_POST (000 and 001) merged into a duplicate NSCID and Customer Flag: ""0"" ) Need to display as ""Two SCIDs merged into a confirmed SCID" CASE 2: (When two different policies were assigned to SCID_POST (000), but under different NSCID is assigned to Customer Flag: ""0"") Need to Display as "" Confirmed SCID has been Unmerged" CASE 3: (When Client Type: Insured is the same(SCID_PRE and SCID_POST) as policy holder (SCID_PRE and SCID_POST), but under NSCID assigned different ID numbers and Customer Flag is: ""0"" and ""1"") Vise Versa (When Client Type: Insured is the different(SCID_PRE and SCID_POST) as policy holder (SCID_PRE and SCID_POST), but under NSCID assigned same ID numbers and Customer Flag is: ""0"") Need to display as ""Insured Info Mismatch" Expected OUTPUT: Here is the expected output to be display in POWER BI. Please help. Sample Data Reocrds: Here is the sample records can work for this expected OUTPUT. NOTE: For SCID_POST (000 and 001) from power query editor can achieve with help of custom column: =Number.ToText([SCID_POST],"d3") Product Code Policy Client Type NSCID Unconfirmed Customer Flag SCID_PRE SCID_POST 502 6369188 Insured 10001135001 0 63948950 001 500 7417992 PolicyHolder 10001135001 0 63948950 000 500 8567299 Insured 10001059001 0 63950734 000 500 8439184 Insured 10001059001 0 63950734 001 501 4041712 Insured 10001225001 0 63949029 000 501 4041711 Insured 10001225001 0 63949029 001 484 3602011 Insured 10001327001 0 20030916 000 484 3603002 Insured 10001328001 0 20030916 000 484 3603002 Insured 10001328001 0 20030916 000 484 3603005 Insured 10001329001 0 20030916 000 501 9922164 PolicyHolder 10000898001 0 63950560 000 501 9922163 Insured 10000247901 0 63950560 000 501 9922162 Insured 10000898001 0 63950560 000 501 9922162 Insured 10000898001 0 63950560 000 501 9922163 PolicyHolder 10000898001 0 78526740 001 500 7143845 Insured 10000932001 0 63685171 001 500 7143845 PolicyHolder 10000937001 1 63685171 001 500 1266967 PolicyHolder 10001238001 0 63691789 001 500 1266967 Insured 10001242001 1 63691789 001 501 9922151 Insured 10001210001 0 63950555 000 500 7527515 Insured 10001216001 0 63949292 000
