Dynamically show data from current selection in slicer till max year in data.
Hello Community! I have a business requirement where I have to show data as follows- Suppose I have year slicer, when I select a year from the slicer then it should show data from selected year till current year (i.e 2017) in this case. Value will be added up in each row as shown. If I select 2017 then it should show only 2017-18 data. It should be dynamically changing with the year selected in the slicer till max of year. Can anyone help and guide me in this? Thanks in advance!
Count of Occurrences of a measure
In PowerBI, I am trying to figure out how to perform a count of stores based upon the percentage groups PCNT_Measure = DIVIDE(SUM('Table'[Visit]), SUM('Table'[Schedule])) For example, if all 4 months are selected The desired result would be: 100% = 3 stores 75% = 1 store 50% = 2 stores This count/percentage would then change if different months were selected. For example, if only months 1 through 3 were selected... ...then the count would be: 100% = 3 stores 66.7% = 3 storesRolling values per category
I need help with DAX. I have this table: business unit year_month category amount amount all categories 12 month rolling sum 12 month rolling sum all categories 12 month mov avg % PS 202206 A 4 10 4 10 40% PS 202207 A 6 9 10 19 53% PS 202208 A 6 10 16 29 55% PS 202209 A 3 4 19 33 58% PS 202210 A 9 18 28 51 55% PS 202211 A 3 7 31 58 53% PS 202212 A 0 0 31 58 53% PS 202301 A 5 12 36 70 51% PS 202302 A 11 21 47 91 52% I have filtered Year-month >= 202206 because column category is blank before this date. I have filtered on 1 business unit and 1 category, but there are more in de dataset. Amount all category = amount of all categories in business / year-month. I have a date table. I need the Dax formula for the 12 month rolling sum. This is wat I have thus far: 12 month rolling sum = var amount = COUNT('Table'[category]) Return CALCULATE (amount, DATESINPERIOD (Calendar[DATE],LASTDATE ( Calendar[DATE] ),-365,DAY)) But it gives my the same as column amount.
Previous 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.Dynamic 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-2022ADDCOLUMNS and SUMMARIZE - incorrect Total (Live Connection)
Please see below issue: As you can see, for the No of Sessions, the total should be 9, but i'm getting a total of 32 insead which is super weird. Since we're using a Live Connection, I don't have access to calculated columns. See below DAX I used: No of Sessions = VAR SessionsTable = ADDCOLUMNS ( SUMMARIZE ( 'Location-Practice-Rendering', 'Location-Practice-Rendering'[LeafName] ), "Total Encounters", [Encounters], "No of Days Total", [No of Days], "No of Sessions Per Day", IF ( [Encounters] <= 2, 0, IF ( [Encounters] > 10, 2, 1 ) ) ) RETURN SUMX( SessionsTable, [No of Days Total] * [No of Sessions Per Day]) Where [Encounters] and [No of Days] are measures: Encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" )) No of Days = CALCULATE ( DISTINCTCOUNT ( 'Time'[CalendarDate]), FILTER ( 'Time', 'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" ) ) We created the following DAX measure which gives us the correct results, BUT it takes hours to load, where as my DAX above takes seconds. The following DAX gives us correct results: No Of Sessions Day = VAR _encounters = CALCULATE ( DISTINCTCOUNT ( 'Transaction Attributes'[Encounter Number__EBM__Encounter Number] ), FILTER ( 'Transaction Attributes', 'Base Measures'[Encounters] > 0 ) ) RETURN SWITCH ( TRUE (), _encounters <= 2, 0, _encounters >= 2 && _encounters <= 10, 1, _encounters > 10, 2 ) Total No of Sessions = VAR SessionsTable = SUMMARIZE ( 'Fact', 'Fact'[CustomerID], 'Fact'[CalendarDate], "No of Days Total", CALCULATE ( DISTINCTCOUNT ( 'Fact'[CalendarDate] ) ), "No of Sessions Per Day", [No Of Sessions Day] ) RETURN SUMX ( SessionsTable, [No of Days Total] * [No of Sessions Per Day] ) Here are the results from the above DAX: Can you please help me amened my original DAX so I can get the correct results? The ADDCOLUMNS SUMMARIZE works a lot faster. Unless we can fix the second DAX to make it faster? amitchandak - you have any thoughts on this issue? Any help would be much appreciated 🙂Measure in Live Connection - Incorrect Total
Hello, I know there have been many solutions on incorrect totals from measures in Live Connection, but I can't seem to figure out the solution for my measure: No of Sessions = VAR _encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" ) ) VAR _NoOfDays = Calculate(DISTINCTCOUNT('Time'[CalendarDate]),Filter ('Time', 'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" )) VAR _NoOfSession = _NoOfDays * SWITCH ( TRUE (), _encounters <= 2, 0, _encounters >= 2 && _encounters <= 10, 1, _encounters > 10, 2 ) RETURN _NoOfSession This gives me the following result (I have covered up the practice locations): You can see the No of Sessions total is incorrect. I even attempted something different by trying something different. I wanted to try something like DISTINCTCOUNTX (but this does not exist in DAX) - so I found the below solution: No of Sessions New = VAR _NoOfDays = COUNTROWS( DISTINCT( SELECTCOLUMNS( 'Time', "CalendarDate",'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" ))) VAR _NoOfSession = _NoOfDays * IF ( [Encounters] <= 2, 0, IF([Encounters] >= 2 && [Encounters] <= 10, 1, 2 )) RETURN _NoOfSession I replaced the VAR _encounters with a measure instead [encounters] . And I replaced the SWITCH statement for an IF statement instead to see if that will do anything different, but it still doesn't work: The following is the Encounters measure (it's exactly the same as in the original No of Sessions DAX: Encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" )) Please help me write a new DAX measure that will fix this issue and give me correct Totals 🙂
