Deduct values until it turns 0 then use 0
Dear friends, I am working on a measure called 'remainingQuanity' that deducts the [Quantity] from [Inventory Units]. Sample table: Index remainingQuantity inventoryUnits Quantity 82078 3 4 1 82079 4 4 -1 82080 3 4 1 82081 2 4 1 82082 1 4 1 82083 0 4 1 82084 -1 4 1 82085 -2 4 1 82086 -3 4 1 82087 -4 4 1 Once the [remainingUnits] becomes 0 it should use 0 instead of the calculation: It's sound so easy, but what's the right dax? Thank youHow to make a count of unique registrations per day?
I have the following table, where I need to make a measurement that relates the two columns: A) CPF B) Registration date What I need to do is the following: I have a registration database, where people register using their CPF and email, and my database records the days they registered. I need to make a measure where I can count how many unique CPF's registered on each day, because I need to use this number to subtract with another measure later. In other words, what I want is a measure where I can see per day how many registrations they had on the day, so if I throw this into a table I can see per day how many unique registrations they had, instead of the table showing me just the total. Can someone help me with this?
Specific count with condition and runs need to add
Hi Everyone, I have a problem and i am unable to fix that. In my report , i have code column that contains these extension codes /LH , /LE,/LS/ and /DE,/DS,/DH. In every month many codes were created, now the scenario is, i need to count only /L extension codes and if count =1 or 2or3or4, we need add runs like this count of code * 5 runs and if count >= 10 or more we need to add 50 runs, 50 is final. These runs i need to add to a measure and this shold be happened in every month. i have tried this formula, but not getting output , rowscounting 2 = VAR LCOUNT = CALCULATE( 'CARL_CS02 CSWO_WO'[WO COUNT],# count of code FILTER( 'CARL_CS02 CSWO_WO', 'CARL_CS02 CSWO_WO'[WO FILTER] in {"/LH WO","/LS WO","/LE WO"}#WO FILTER is custom column holding just extension of code ) ) VAR ADDINGRUNS = IF(LCOUNT = 0, 'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 1, 5+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 2, 10+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 3, 15+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 4, 20+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 5, 25+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 6, 30+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 7, 35+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 8, 40+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT = 9, 45+'DAX FORMULAS'[L1-SCORE], IF(LCOUNT >= 10, 50+'DAX FORMULAS'[L1-SCORE],0 ))))))))))) RETURN LCOUNT please let me know is their any way to fix and to get correct output. Thanks for your contribution, co-operation and helping
Calculate running totals between 2 date
Hi, I'm trying to calculate the running totals between my period slicer. Atm. I can only manage to filter on max day selected and not the min with the following formula: [Total Budgetupdated] = A calculated Measure Period[Date] = My period tabel which the slicer isreferencing from. Cumulative Total Budget = CALCULATE ( [Total Budget updated], FILTER ( ALL ( Period[Date] ), Period[Date] <= MAX ( ( Period[Date] ) ) ) So my goal is to incorporate Period[Date] >= MIN ( ( Period[Date] ) In my measure.
Ignore Page Filter in Measure
I have created the following cards at the top of my report: I have added a Page level filter on NET SALES: Level 1 is a hierarchy and Sales exists within Net Sales hence why the card visual has not gone BLANK. But my other cards from Sales + Open Orders to Contribution Margin have gone blank. I have tried using REMOVEFILTERS as below: Sales + Open Orders = CALCULATE( [Amount], 'Profitability Account'[Description] = "Sales + Open Orders", REMOVEFILTERS('Profitability Account'[Description]) ) I even added Level 1 in remove filters but that doesn't work either. How can I fix this? Thanks, SPrevious Year YTD (Cluster Column Chart)
My PY Encounters is showing the total (JAN - DEC) value for all months in cluster column chart: I need it to show the correct amount per month here is the DAX: Encounters_PY = CALCULATE( [Encounters], FILTER( ALL('Date - Service'), YEAR('Date - Service'[CalendarDate]) = YEAR( MAX( 'Date - Service'[CalendarDate]) )-1 ), ALL ( Scenario[Description] ) ) Can anyone please help me? Thanks in advance.
How can I find the difference between two filtered values?
Hello! I'm from Brazil, so I'm sorry if some images are in portuguese or if my english is bad (and I'm also new to Power BI so be patient with me hehe). But basically i need to calculate the difference between two values, but each of them are going to be switching according selection on an external filter. To better understand my issue: 1) My data base is a bunch of Index columns (1201,1202,1203...) with different values in each line that correspond to a date. So on March/2013, the index 1201 was 100,524. On April/2013 was 101,485, and so on. 2) I'd like to create some sort of calculator, so Power BI could show me the difference between values of an index on different dates. For example: The index 1201 value on march/2013 = 100,524 The index 1201 value on march/2014 = 113,289 The difference = 12,765 The thing is that the date is going to be changing at all times through an external filter, so the subtraction has to change accordingly. HOW I'VE TRIED TO DO ( and failed D; ) At first I've created two measures to return the values already being effected by the filters. For example, the measure "Index 1201 according Data Filter" returns to me the index 1201 value according to the selected date, in this case, april of 2013. I used CALCULATE and ALLSELECTED to do that. The other measure is basically the same, but it's another filter that also corresponds to a date but through a Project Number (i guess the data base in this case it's not relevant). After that i've tried to create the measure below but it's not working: Could you guys help me? Is it possible to do what I am trying to do on Power BI? There's a better way to do that? https://drive.google.com/file/d/1c5DpEI4rsoPX8U8EBepHJ9gSsSmckLmg/view?usp=sharingDynamic Days calculation from 2 dates
Hi Guys, i have a complex scenario as explained below. i want to pull a weekly report for employees who applied for no of days of WFH in a week. i want to find this based on WFH applied dates. can you please tell me what is the best approach to capture the no of days for the selected week? from the below example is Out put1 or Out put2 is possibile? how to achieve both the outputs? But we need to calculate the no of days only for the given week, an employee can apply wfh for multiple days but we need to count the days only for the given week based on the from and to date. tamerj1 amitchandak Jihwan_Kim daXtreme Greg_Deckler Pragati11 mwegener Employee Number Applied on From date To Date Expected Output 2 WFH Date 12345 20-08-2022 25-08-2022 26-08-2022 12345 25-08-2022 34567 25-08-2022 26-08-2022 31-08-2022 12345 26-08-2022 87969 22-08-2022 23-08-2022 06-09-2022 34567 26-08-2022 34567 27-08-2022 Reporting week Aug 22nd to 26th 34567 28-08-2022 Expected output 1 34567 29-08-2022 34567 30-08-2022 Employee Number No of days of WFH applied for the week 34567 31-08-2022 12345 2 Days 87969 23-08-2022 34567 1 Day 87969 24-08-2022 87969 4 Days 87969 25-08-2022 87969 26-08-2022 87969 27-08-2022 87969 28-08-2022 87969 29-08-2022 87969 30-08-2022 87969 31-08-2022 87969 01-09-2022 87969 02-09-2022 87969 03-09-2022 87969 04-09-2022 87969 05-09-2022
