identifying active users every month
Hi, I have a list of users every month, and i need to find the active users in every month. maybe by creating a new calculated column which states if the user is active or not - If the user names present in the next available month file then those user names are active or - if a new user appears for the very first time which are not available in any of the month files then they are active from that month onwards. Please note if user names not appearing continuously then they are inactive. please find the data like below: column names are: System user name,Count of System user name,Sum of cost,Monthly Source file Name System user name Count of System user name Sum of cost Monthly Source file Name User_A 3 € 112.50 24-Jan User_A 3 € 112.50 24-Mar User_A 3 € 112.50 24-Apr User_A 3 € 112.50 24-May User_A 3 € 112.50 24-Jun User_A 3 € 112.50 24-Sep User_B 7 € 149.50 24-Mar User_B 7 € 149.50 24-Apr User_B 7 € 149.50 24-May User_B 7 € 149.50 24-Jun User_B 8 € 238.50 24-Sep User_C 5 € 139.50 24-Jan User_C 7 € 151.50 24-Mar User_C 7 € 151.50 24-Apr User_C 7 € 151.50 24-May User_C 7 € 151.50 24-Jun User_C 7 € 151.50 24-Sep User_D 12 € 323.00 24-Jan User_D 13 € 329.00 24-Mar User_D 13 € 329.00 24-Apr User_D 13 € 329.00 24-May User_D 13 € 329.00 24-Jun User_D 14 € 332.00 24-Sep User_E 5 € 138.50 24-Jan User_E 7 € 209.50 24-Mar User_E 7 € 209.50 24-Apr User_E 9 € 222.50 24-May User_E 9 € 222.50 24-Jun User_E 9 € 305.00 24-Sep User_F 11 € 362.50 24-Jan User_F 13 € 374.50 24-Mar User_F 14 € 380.50 24-Apr User_F 14 € 380.50 24-May User_F 14 € 380.50 24-Jun User_F 14 € 463.50 24-Sep User_G 6 € 219.50 24-Apr User_G 7 € 226.00 24-May User_G 6 € 219.50 24-Jun User_G 7 € 246.50 24-Sep User_H 3 € 112.50 24-Mar User_H 3 € 112.50 24-Apr User_H 3 € 112.50 24-May User_H 3 € 112.50 24-Jun User_H 3 € 112.50 24-Sep How do i create this Active users list every month using the above data in power bi?? Thanks in advance for the help
DAX Query
Hello Experts, I want to calculate average of a measure NR: NR=VAR CurrentDate = MAX('Dim - Calendar'[Fiscal Year ID]) VAR result = CALCULATE( COUNTROWS('Audit'), FILTER( ALLEXCEPT('Audit', 'Audit'[NAME]), 'Audit'[RATING] = "Needs Refinement" && 'Audit'[FISCAL_YEAR_ID] = CurrentDate ), CROSSFILTER('Dim - Calendar'[Date], 'Audit'[PLANNED_START], None) ) VAR max_notblank = CALCULATE( MAX('Audit'[FISCAL_YEAR_ID]), FILTER( ALLSELECTED('Audit'), 'Audit'[FISCAL_YEAR_ID] < CurrentDate || 'Audit'[FISCAL_YEAR_ID] <> BLANK() ), CROSSFILTER('Dim - Calendar'[Date], 'Audit'[PLANNED_START], None) ) VAR result1 = CALCULATE( COUNTROWS('Audit'), FILTER( ALLEXCEPT('Audit', 'Audit'[NAME]), 'Audit'[RATING] = "Needs Refinement" && 'Audit'[FISCAL_YEAR_ID] = max_notblank) ), CROSSFILTER('Dim - Calendar'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None) ) if(result = blank(),result1,result) for this I have written averagex(values(Audit[Name]),[NR]), here it is getting only result value, eeven I have data for previous year for other names. here for the selected year we only have data for one name, but as per my measureNR, we have 5 records as it is checking previous year too.now I want average of NRlike(6+4+5+10+11)/5=7.2. As averagex is not working I tried to create two different measures like(sumof NR/countof NR),I succeded in creating sum of NR measure but not count of NR please helpDAX query required for IF and IF Else condition of below case
Hi Team, I am new to PBI, help me with this case! IF type(col) = "AAA" & Date time(col) < 11AM then Pickup date = Date(col)-1 ELSE Pickup date = Date(col) IF type(col) = "BBB" then Pickup date = Date(col)–1 IF type(col) = "CCC" then Pickup date = Date(col) Value of "AAA" is having AAA_a, AAA_b, AAA_c, AAA_d, AAA_e ----AAA_n etc. So here we are calling the condition with "AAA". How do we calling "AAA" with one IF condition statement or any other. Same like value of "BBB" & Value of "CCC". Here date time(col) contains 24hrs, we required less than(< )upto 11AM then Pickup date = Date(col)-1, else Pickup date = Date(col). Here Date(col) is calculated column. So, kindly helpe me with the DAX query. below is the snapshot for your reference.DAX Query
Hello Experts, I want to write a dax measure for the following scenario: Here, I have a slicer named "Fiscal Year" that contains data like 2019/2020, 2020/2021, etc., in text format. If I select 2023/24 from the slicer assuming as a current slicer, here the latest audit happened for Biscuits for Adequate is 2022/23 and now I want count od Adequate for previous Audit. if there is no previous audit then it should display na. how can write this using dax. I have placed sample dataset for better understanding. https://www.dropbox.com/scl/fi/5k46fdr2b2kwrkwyd1mwv/test.pbix?rlkey=gs3e020c91pj4mdwtt5kcsv8t&st=8b7ml66c&dl=0 TIA
Optimizing Measure performance (open incidents running total)
Dear All, A couple of days ago I posted a topic for help with a measure which calculates the number of open incidents per day. I've already made some small adjustments since then. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Open-Incidents-per-day/m-p/3968348 Objective of this measure is to calculate to number of incidents where the last status in the filter context of the visual is not closed. The measure I created works in the sense that it shows the correct results. However, already in my test-environment which is only ~15.000 rows, the performance isn't great. When I attempt to run the measure in my live environment with ~15.000.000 rows, it's completely un-useable, as in, I just get an error that it runs out of memory. As far as I can see there are 2 issues: The number of iterations on the facttable A rolling date range is intensive as the datasize keeps getting larger day-by-day (_Date[Date] <= MaxDate) I'm however at a loss on how to calculate this result in a different way. What would be the correct way to re-write this measure so that it's usable in a larger data-model? For reference, my fact table (Raw_Transactions) looks something like the screenshots below. Only connected table is a date table called '_date'. Thanks in advance for the assistance! Open Incidents (Summarize) = VAR MaxDate = MAX('_Date'[Date]) VAR Result = CALCULATE( COUNTX( ADDCOLUMNS( SUMMARIZE( Raw_Transactions, Raw_Transactions[IncidentId], Raw_Transactions[CountOfTransaction] ), "@MaxTransaction", VAR _Result = CALCULATE( MAX( Raw_Transactions[CountOfTransaction] ), ALLEXCEPT( Raw_Transactions, Raw_Transactions[IncidentId] ), _Date[Date] <= MaxDate ) RETURN IF( _Result = Raw_Transactions[CountOfTransaction], _Result ) ), [@MaxTransaction] ), _Date[Date] <= MaxDate, Raw_Transactions[TransactionType] <> "Closed" ) RETURN Result
How to sum a value and exclude anything that is over 1 month old
How do I create a measure that sums the total of expected revenue but excludes anything over 2 months old. I only want to include the previous month and any dates moving forward. Example outlined in red below. I have a calendar table in my data model.Switch Statement to multiply different measures based on category name
I am trying to create a DAX measure to calculate the Revenue Forecast based on the age of opportunities. Each age category has a different conversion rate based on historical rates. I placed the DAX below in a calculated column and it comes through in a card visual but not on the table which is where I need it. I have tried both IF and SWITCH statements. I think I need it to be a measure but the measure is not seeing my Age Category column. Revenue Forecast = SWITCH( TRUE, 'Opportunity'[Age Category] = "0-90 Days", ([TotalRevenue] * [90DayRateWon] * [Conversion Rate R12M]), 'Opportunity'[Age Category] = "90-180 Days", ([TotalRevenue] * [180DayRateWon] * [Conversion Rate R12M]), 'Opportunity'[Age Category] = "180-270 Days", ([TotalRevenue] * [270DayRateWon] * [Conversion Rate R12M]), 0) For the first category 0-90 Days, I want it to take $260,573.54 X [90DayRateWon] X [Conversion Rate R12M] etc...Thank you for taking the time.
